Monday, 24 December 2018

Mysql: Administering MySQL Database Server

5.  Administering MySQL Database Server

5.1  Managing User Accounts

To create a new user, use CREATE USER command as follows:
CREATE USER 'username'[@'userhostname'] IDENTIFIED BY PASSWORD 'password';
The default userHostname is localhost. You could use either DNS hostname or IP address. Wildcard '%' and '_' can be used for userHostname, e.g., 'peter'@'%' (for all hosts), 'paul'@'*.abc.com', 'pris'@'128.1.2.%'. For IP address, a netmask can be specified in IPAddress/Netmask, e.g., '192.168.1.0/255.255.255.0'.
The new user created has no privileges. You need to grant the appropriate privilege to the user using GRANT command. Privilege can be grant globally (for all tables of all databases), or at the database-level, table-level, column-level. For example,
-- Global privileges (all tables of all databases)
-- Grant all privileges (except the GRANT privilege) on all the databases all the tables (*.*)
GRANT ALL ON *.* TO 'username'@'userhostname';
-- Grant all privileges, including GRANT privilege
GRANT ALL ON *.* TO 'username'@'userhostname' WITH GRANT OPTION;
-- Grant selected privileges on all the databases all the tables
GRANT SELECT, INSERT ON *.* TO 'username'@'userhostname';
   
-- Database-level
-- Grant selected privileges on all the tables of a particular database
GRANT SELECT, INSERT ON databaseName.* TO 'username'@'userhostname';
   
-- Table-level
-- Grant selected privileges on selected tables of a particular database
GRANT SELECT, INSERT, UPDATE, DELETE ON databaseName.table1Name, databaseName.table2Name TO 'username'@'userhostname';
   
-- Column-Level
-- Grant selected privileges on selected columns of tables of a particular database
GRANT SELECT(column1Name, column2Name), INSERT ON databaseName.tableName TO 'username'@'userhostname';
The available privileges are:
  • Object Rights: SELECT, INSERT, UPDATE, DELECT, EXECUTE, SHOW VIEW.
  • DDL Rights: CREATE, ALTER, DROP, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE, REFERENCES, INDEX, FILE.
  • Others: ALL, ALL WITH GRANT OPTION, GRANT, CREATE USER, CREATE TEMPORARY TABLES, LOCK TABLES.
To remove privileges, use REVOKE command, e.g.,
-- Remove all privileges
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'userhostname';
You may need to issue a "FLUSH PRIVILEGES" command to clear and reload temporary caches in MySQL, for the new privileges to take immediate effect.
To remove a user, use DROP USER command as follows:
DROP USER 'username'@'userhostname';
To change the password of a user, use SET PASSWORD command, e.g.,
SET PASSWORD FOR 'username'@'userhostname' = PASSWORD('newPassword');
-- For current user
SET PASSWORD = PASSWORD('newPassword');
To rename a user, use RENAME USER command, e.g.,
RENAME USER 'username'@'userhostname' TO 'newUsername'@'userhostname';
Security Considerations on User Management
  • The superuser root has no initial password. It is critical to protect superuser root with a strong password.
  • Remove the anonymous user (identified by an empty string) ''@'localhost', or ''@'127.0.0.1', which was created during the installation by default.
  • All new users must be password protected.
  • Never choose a password from dictionary. Use a combination of letters and numbers.
  • Do not grant any user (except root) access to mysql.user table (because passwords are stored in this table).
  • Do not grant more privileges than necessary. Grant the least privilege necessary to carry out the tasks.
  • Never grant privileges to all hosts.
  • Run "SHOW GRANTS [FOR 'username'@'hostname']" statement to list the privileges granted to a user (default to current user). For example,
    -- List the privilege for the current user
mysql> SHOW GRANTS;
-- List the privilege for another user
mysql> SHOW GRANTS FOR 'username'@'hostname';
    You can also run the statement in batch mode with execute (-e) option:
    Shell> mysql -u username -p -e "SHOW GRANTS FOR 'selectedUser'@'hostname'"
    Alternatively, you can list the privileges (vertically) by querying the mysql.user table.
    mysql> SELECT * FROM mysql.user \G
  • Run your database server behind a firewall (or in DMZ), and block the database server port number (default 3306) from untrusted hosts. Try "telnet databaseHostname 3306" from a remote machine to confirm that it is not opened to untrusted hosts.
  • Do not run mysqld or mysql daemon/service/program with privilege user of the system (e.g., root of Unix, administrator of Windows). Create a normal user (say mysql) to run the database server mysqld. This user requires read and write privilege to the MySQL directory and data directory.
  • MySQL supports internal SSL. Use SSL to encrypt the messages (in transit) if necessary.

5.2  Logs

MySQL maintains several logs to help you maintain your database server: Error Log, Binary Log, General Query Log, Slow Query Log. All logs, by default, are kept in the data directory.
Error Log
The error log maintains all the startup, shutdown, and critical operating error messages. Error log in enabled by default. The error log file is called "hostname.err" in the data directory. You can change the error log file via --log-error=filename mysqld startup option.
On Windows, if you use --console option, the error will be written to the stderr (which defaults to console) instead of error log file.
Binary Log
The binary log keeps all statement that changes the database, e.g., CREATE TABLE, INSERT, UPDATE, DELETE. It does not keep query statements (such as SELECT, SHOW, DESCRIBE) that does not change the database. The binary log can be used to replicate a slave backup server, or to bring a backup database up-to-date, by executing all the changes logged. To enable the binary log, start MySQL server mysqld with the --log-bin=baseName option. A number is appended behind the baseName. The server creates a new log file with the next number each time it starts or whenever the log is flushed. An index file is also created to keep track of all the binary logs.
There are three logging formats:
  • Statement-based Logging: specify via option --binlog-foramt=STATEMENT. The logged statement can be propagate to a slave or backup server.
  • Row-based Logging: specify via option --binlog-foramt=ROW. Keep track of how individual rows were changes.
  • Mixed Logging: specify via option --binlog-format=MIXED. It uses statement-based logging by default, but switch into row-based in certain cases to improve efficiency.
You can display the binary logs using the mysqlbinlog utility, which converts the log entries into SQL statements:
-- Display on console
Shell> mysqlbinlog logfile.001 [logfile.002...] | more
-- Saved in a text file
Shell> mysqlbinlog logfile.001 [logfile.002...] > "d:\path\binlog.sql"
To update a backup server, pipe the output of mysqlbinlog to a mysql client:
Shell> mysqlbinlog binaryLogFile[s] | mysql -u userName -p
General Query Log
The General Query Log maintain information about clients' connections, and all SQL statements. To enable, use mysqld startup option --general_log=filename (or --general_log with default filename of hostname.log). The log can be directed to a file (default), or a table (mysql.general_log), or both, or disabled via an additional option --log-output=FILE|TABLE|FILE,TABLE|NONE.
Slow Query Log
Keep track of SQL statement that took more than long_query_times (default of 10 seconds). You could enable via mysqld startup option --log_slow_queries=filename. Similar to General Query Log, it can be directed to a file (default), or a table (mysql.slow_log), or both, or disabled via an additional option --log-output=FILE|TABLE|FILE,TABLE|NONE.
Maintaining the Log Files
Log files grow, and need to be maintained periodically. You can remove the error log and the query logs, but not the binary log as that is used for backup and recovery.
You can issue command "FLUSH LOGS" to close and reopen all the log files. For error log, the old log file will be renamed with suffix "-old" (which could be removed) and a new file created.
For binary log, a new file with the next number will be created, when the log is flushed. For the binary log, you can set the expire_logs_days to expire binary log files automatically after a given number of days. Make sure that the replication or backup are updated before the binary log expired.
The general query log, slow query log and binary log may log statements containing password. For security consideration, there should be place in dedicated directory (e.g., "/log") with access to the database server and the administrator only.
[TODO] Script and procedure for maintaining log file, backup and recovery
Notes for InnoDB
In the data directory, ibdata1 contains your InnoDB database and ib_logfile0 and ib_logfile1 are log files for InnoDB. The two 5MB log files are used in a circular manner.

5.3  Backup and Recovery

There are two types of backups: logical backup and physical (raw) backup.
  1. Logical Backup: Save Information about the logical database structures (e.g., CREATE TABLE) and contents (e.g., INSERT). Larger output but the backup is platform-independent and portable. You can run the backup online, at the server-level (all databases), database-level (all tables of a database), or table-level (all rows and columns of a table). You can use "mysqldump" utility or "SELECT ... INTO OUTFILE" statement for backup; and "mysqlimport" utility or "LOAD DATA INFILE" statement to restore.
  2. Physical (Raw) Backup: Backup the physical files (data directory). You can simply copy the data directory (offline), or use utilities "mysqlhotcopy" (for MyISAM tables), "ibbackup" (for InnoDB tables) (online).
"mysqldump" Utility
"mysqldump" can be used to back up the entire server, selected databases, or selected tables of a database. It generates a text file of SQL statements that can later be executed to recreate the database/table and its contents.
-- Dump a particular database (all tables)
Shell> mysqldump -u username -p databaseName > backupFile.sql
-- Dump selected tables of a particular database
Shell> mysqldump -u username -p databaseName table1Name [table2Name ...] > backupFile.sql
-- Dump several databases with --database option
shell> mysqldump --databases database1Name [database2Name ...] > backupFile.sql
-- Dump all databases in the server with --all-databases option, except mysql.user table (for security)
shell> mysqldump -u root -p --all-databases --ignore-table=mysql.user > backupServer.sql
You can restore from the backup by running client "mysql" in batch mode to execute the SQL statements:
Shell> mysql -u username -p databaseName < backupFile.sql
or using the source command in an interactive client:
-- Run an mysql interactive client and load in to MySQL server
mysql> source d:/path/backupfile.sql
Example: The following command backup the table employees and customers of the database southwind_mini.
Shell> mysqldump -u username -p southwind_mini employees customers > "d:\path\backup.sql"
Study the output file, which contains CREATE TABLE and INSERT statements to recreate the tables dumped.
Incremental Backup using Binary Log
Enable the binary log, by running the server mysqld with option --log-bin=baseName.
To make an incremental-backup from the last full-backup or incremental-backup. First flush the log (FLUSH LOGS) to rotate the binary log. Then, use utility mysqlbinlog to convert the log entries to SQL statements and pipe into a mysql client.
Shell> mysqlbinlog logfile.001 [logile.002 ...] | mysql -u root -p
Backup & Recovery Strategies using mysqldump and Binary Log
InnoDB storage engine supports transaction and automatic crash recovery. Suppose that MySQL server was terminated abnormally (e.g., power failure, system crashes). Upon restart, the InnoDB engine reads its logs for pending committed and non-committed transactions. It flushes the data for those pending committed, and rollback the uncommitted transaction. It records all its actions in the error log.
However, if MySQL data disk is damaged, we need to recover the database from the latest full-backup and binary log (hopefully not damaged), a straight backup/recovery policy is needed.
Suppose that we run a full backup every Sunday at 3:00am, and incremental backup every day at 3:00pm.
  1. Enable binary log by starting the server with --log-bin=baseName option. Place the binary log at the secure and safe media (e.g., RAID disk) if available, so that it can be used in case of catastrophic disk failure.
  2. To take a full backup, run mysqldump with these options:
    Shell> mysqldump --single-transaction --flush-logs --delete-master-logs --master-data=2
                 --all-databases [--ignore-table=mysql.user] > fullbackup.sql
    • The --flush-logs option closed the current binary log, and create a new binary log using the next number. The new binary log filename is recorded in the dump (which is needed for recovery). The --delete-master-logs removes the older binary logs (as they occupy a lot of spaces). The --master-data=2 write the binary log file name and position to the dump as a comment.
    • The --single-transaction option prevent updating during the dumping operation.
    • The --all-databases --ignore-table options backup the entire server except mysql.user table for security (as it contains passwords). You may back up mysql.user if the backup files are secure. Take note that the binary log may also contain password information, and must be secured.
  3. To take an incremental-backup, issue command "FLUSH LOGS" which closes the current binary log, and create a new binary log using the next number. Copy the closed binary file to a safe media and location! You need it to run the recovery.
  4. Suppose that there is a catastrophic crash, and the MySQL data file is not usable. You may need to reformat the hard disk, re-install the operating system, re-install MySQL, and Follow this procedure to recovery the databases:
    1. Restore the full-backup.
      Shell> mysql -u root -p < fullbackup.sql
    2. Restore the incremental-backup from binary logs: Check for all the available binary logs. The last binary log at the time of crash may or may not be usable. Use the mysqlbinlog utility to process all the binary log files at once, and pipe the output SQL statement to a mysql client.
      Shell> mysqlbinlog binlog.x+0 [binlog.x+1 ...] | mysql -u root -p
    Practice the recovery procedures to create a "test" database or for development purpose.
"mysqldump" with Table Output
You can use option '--tab=filename' to direct mysqldump to backup the data in table format (instead of CREATE TABLE and INSERT statements). For example,
Shell> mysqldump -u username -p --tab="d:\path" southwind_mini employees customers
Two file are created in the specified directory for each table dumped. In our example, "employees.txt" for the data and "employees.sql" for the CREATE TABLE statement. The text file uses 'tab' as column delimiter, '\n' as the line delimiter, and '\N' for NULL.
SELECT ... INTO OUTFILE
Use for backing up the contents of a table or selected columns and rows of a table.
SELECT * INTO OUTFILE 'filename' FROM tablename
SELECT columns INTO OUTFILE 'filename' FROM tableName WHERE criteria
Example:
-- Create a test table
DROP TABLE IF EXISTS `backup_arena`;
CREATE TABLE IF NOT EXISTS `backup_arena` (
   `id` SMALLINT(5) UNSIGNED ZEROFILL AUTO_INCREMENT,
   `name` VARCHAR(10) NOT NULL,
   `price` DECIMAL(6,2) NOT NULL,
   `description` VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY(`id`)
);
   
-- Populate with rows
INSERT INTO `backup_arena` VALUES 
  (NULL, 'dummy1', 1.1, NULL),
  (NULL, 'dummy2', 2.2, NULL),
  (NULL, 'dummy3', 3.3, NULL);
-- Make some changes
UPDATE `backup_arena` SET price = price * 0.9;
-- Display the table
SELECT * FROM `backup_arena`;
   
-- Export to a text file
SELECT * INTO OUTFILE 'd:/path/backup_arena.txt' FROM `backup_arena`;
The output file has no column header, uses 'tab' as column delimiter, '\n' as the line delimiter. NULL is represented as '\N', as follows:
00001	dummy1	0.99	\N
00002	dummy2	1.98	\N
00003	dummy3	2.97	\N
LOAD DATA INFILE
We can use "LOAD DATA INLINE" to import data from a text file into a database table. The default column delimiter is 'tab'; default line delimiter is '\n', and NULL is represented as '\N'. "LOAD DATA INFILE" can be used to restore data exported via "SELECT ... INTO OUTFILE".
Example: Suppose that we want to load a text file which has a different format from the table.
We shall use the same table backup_arena created earlier. The text file to be loaded is as follows. It contains a header row, two columns (name and price) need to be loaded, and two extra columns. The column delimiter is ',', line delimiter is '\r\n' (created in Windows). We also want to raise the price by 10%.
NAME,EXTRA,PRICE,ANOTHER EXTRA
pen,,1.23,---
pencil,,0.45,---
paper,,0.12,---
paper clip,,0.05,---
We could use the following LOAD DATA INLINE command:
LOAD DATA INFILE 'd:/path/loaddata_arena.txt' INTO TABLE `backup_arena`
  COLUMNS TERMINATED BY ',' 
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES
  (name, @dummy, @oldPrice, @dummy)
  SET price = @oldPrice*1.1;
The "COLUMNS TERMINATED" and "LINES TERMINATED" clauses are used to specified the column delimiter and line delimiter, respectively. "IGNORES 1 LINES" ignores the header row of the file. The first column of the file are loaded into table columns "name". The third column of the file is assigned to a user-defined variable @oldPrice. The price column of the table is then set to @oldPrice*1.1. The other columns of the text file is assigned to an user-defined variable @dummy, which is not used and discarded.
The SELECT command confirms that the table is properly loaded:
mysql> SELECT * FROM `backup_arena`;
+-------+------------+-------+-------------+
| id    | name       | price | description |
+-------+------------+-------+-------------+
| 00001 | dummy1     |  0.99 | NULL        |
| 00002 | dummy2     |  1.98 | NULL        |
| 00003 | dummy3     |  2.97 | NULL        |
| 00004 | pen        |  1.35 | NULL        |
| 00005 | pencil     |  0.50 | NULL        |
| 00006 | paper      |  0.13 | NULL        |
| 00007 | paper clip |  0.06 | NULL        |
+-------+------------+-------+-------------+
7 rows in set (0.00 sec)
"mysqlimport" Utility
mysqlimport performs the same function as "LOAD DATA INFILE" to load data from a text file into a table. "LOAD DATA INFILE" runs inside an interactive client, whereas mysqlimport runs from command-line.
Shell> mysqlimport databaseName table1Name [table2Name ...]
For example,
Shell> mysqlimport southwind_mini customers.txt employees.txt
Load the data from "Customer.txt" into table customers, "employees.txt" into table employees of the database southwind_mini. The table name is derived from the file name by ignoring the file extension.

5.4  Security

Don't run the MySQL server as superuser root
If you run MySQL server as root, it has root privilege (e.g., can access all the files in the system). Run MySQL server as an ordinary, unprivileged user. For example, in Ubuntu, a system, unprivileged user called mysql is created to run the MySQL server.
// Check the owner of "mysql" process
$ ps aux | grep mysql
mysql  ..... /usr/sbin/mysqld

// Show user "mysql"
$ id mysql
uid=115(mysql) gid=125(mysql) groups=125(mysql)
   // user "mysql" belongs to group "mysql" only.

// Check ownerships and permissions of MySQL data directory
$ sudo ls -l /var/lib/mysql
-rw-rw---- 1 mysql mysql 18874368 Dec 19 16:11 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Dec 19 17:00 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Dec 19 17:00 ib_logfile1
drwx------ 2 mysql mysql     4096 Oct 26 21:49 mysql
drwx------ 2 mysql mysql     1024 Dec 16 03:49 test
......
   // user-owner: mysql
   // group-owner: mysql
   // Data sub-directories: list-write-access by "mysql" only.
To specify the owner of the mysql process, you need to configure /etc/mysql/my.cnf as follows:
[mysqld]
user            = mysql
datadir         = /var/lib/mysql
The MySQL data directory shall be owned and only be accessible by server's account
As seen in the above example, the data directory is only accessible by the user "mysql". Other users have no access.
[The location of the data directory is configured in the "datadir" directive of the configuration file "/etc/mysql/my.cnf" as illustrated above.]
To set the ownerships and permissions:
$ cd /path/to/MySQL-datadir
$ sudo chown -R mysql:mysql .
    // -R recursive
    
$ sudo chmod -R 700 .
    // 700 = rwx --- ---
MySQL configuration file shall be properly protected
The global configuration file /etc/mysql/my.cnf shall be owned by root, with permissions (rw- r-- r--) or 644. That is, it is writable by root (or sudo), but readable by the world (both server and client program need to read this configuration). You should be keep confidential data such as password as it is world-readable.
The local configuration files (my.cnf located at data directory) shall be owned by user "mysql" and read-write only by "mysql" (i.e, 600).
Remove the test database.
MySQL user accounts
  • Remove all anonymous account.
  • Password protect MySQL root account.
  • Grant only the necessary privileges (up to table-level or column-level) to My SQL user account. [TODO] example.
Password Policies
  • All accounts shall be password protected.
  • Password shall be stored in hash, so that even the administrator cannot see clear-text password.
  • Add a salt to the password hash, which is stored together with the password hash. Different user shall have a different salt, so that the hash is different even if the password is identical.
Backup Regularly
Use a cron job (configured in crontab)to back up the database regularly, and preferably rsync or scp to another server.
$ mysqldump -u root -p databasename > backup.sql
$ rsync -azv --rsh=ssh src dest
   // over the ssh connection

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)