mysql

The basic installation of MySQL Server provides a great command line utility to take backup of MySQL databases

The ‘mysqldump‘ command is used for creation “dumps” of databases managed by MySQL.

These dumps are just files with all the SQL commands needed to recreate the database.

Backup a Database

Use the following command to backup a database:

$ mysqldump --opt -u [username] -p[password] [dbname] > [backupfile.sql]

Note: There is no space between -p and the password.

Example:

$ mysqldump --opt -u root -pSeCrEt customers_db > customers_db_backup.sql

Backup multiple Databases

Use the following command to backup several databases:

$ mysqldump --opt -u [username] -p[password] --databases [dbname_1] [dbname_2] ... > [backupfile.sql]

Example:

$ mysqldump --opt -u root -pSeCrEt --databases customers_db employees_db > backup.sql

Backup all Databases

Use the following command to backup all databases:

$ mysqldump --opt -u [username] -p[password] --all-databases > [backupfile.sql]

Example:

$ mysqldump --opt -u root -pSeCrEt --all-databases > all_db_backup.sql

Backup a Database with a Compression

Use the following command to backup a database with compression:

$ mysqldump --opt -u [username] -p[password] [dbname] |  gzip -9 > [backupfile.sql]

Example:

$ mysqldump --opt -u root -pSeCrEt customers_db |  gzip -9 > customers_db_backup.sql.gz

If you want to extract the .gz file, use the command below:

$ gunzip customers_db_backup.sql.gz

The most common task when managing a replication process is to ensure that the replication is taking place and that there have been no errors between the slave and the master.

The primary statement for this is ‘SHOW SLAVE STATUS‘, which must be executed on each slave.

mysql> SHOW SLAVE STATUS\G

The key fields from the status report to examine are:

• Slave_IO_State – the current status of the slave;
• Slave_IO_Running – whether the I/O thread for reading the master’s binary log is running. Normally, you want this to be ‘Yes’ unless you have not yet started replication or have explicitly stopped it with ‘STOP SLAVE’;
• Slave_SQL_Running – whether the SQL thread for executing events in the relay log is running. As with the I/O thread, this should normally be ‘Yes’;
• Last_IO_Error, Last_SQL_Error – the last errors registered by the I/O and SQL threads when processing the relay log. Ideally these should be blank, indicating no errors;
• Seconds_Behind_Master – the number of seconds that the slave SQL thread is behind processing the master binary log. A high number (or an increasing one) can indicate that the slave is unable to handle events from the master in a timely fashion.

Several pairs of fields provide information about the progress of the slave in reading events from the master binary log and processing them in the relay log:

• Master_Log_file, Read_Master_Log_Pos – coordinates in the master binary log indicating how far the slave I/O thread has read events from that log;
• Relay_Master_Log_File, Exec_Master_Log_Pos – coordinates in the master binary log indicating how far the slave SQL thread has executed events received from that log;
• Relay_Log_File, Relay_Log_Pos – coordinates in the slave relay log indicating how far the slave SQL thread has executed the relay log. These correspond to the preceding coordinates, but are expressed in slave relay log coordinates rather than master binary log coordinates.

On the SLAVE, you can check running processes using ‘SHOW PROCESSLIST‘ command.

mysql> SHOW PROCESSLIST\G

On the MASTER, you can also check the status of connected slaves using ‘SHOW PROCESSLIST‘ to examine the list of running processes.

mysql> SHOW PROCESSLIST\G

Because it is the slave that drives the replication process, very little information is available in this report.