If your MySQL slave is broken/out of sync or you just want to import the master data here are the steps you should take to jumpstart your MySQL replication.
On the slave
mysql> SLAVE REST; mysql> exit
On the master
root# mysqldump --routines --master-data --single-transaction --skip-add-locks --skip-lock-tables --default-character-set=utf8 database > db-backup-`date -I`.sql Now push this dump to the slave server. This one is just if you don't have the user or want to change the password. mysql> grant replication slave on *.* to 'email@example.com identified by 'slave';
On the slave
mysql> drop schema database; mysql> create schema database; root# mysql database < dbdump-date.sql We did the reset so we have to update user information too mysql> CHANGE MASTER TO MASTER_USER='replication', MASTER_PASSWORD='slave'; mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G and look for: Slave_IO_Running: Yes Slave_SQL_Running: Yes
Let’s review these parameters and see their effect:
--user: This is the user which initiates the dump. Depending on other parameters, the user may need to have quite a few privileges, such as
REPLICATION CLIENTetc. Since I do not usually allow for remote root access into mysql, I create a temporary user solely for the purpose of the dump (many times it’s a one-time action), for the specific machine from which the dump is run, and provide this user with all necessary permissions.
--routines: It is really an annoyance to have to remember this flag. In contrast to –triggers, which is by default TRUE, the
--routinesparameter is by default FALSE, which means if you forget it – you don’t get the stored functions and procedures in your schema.
--master-data: I always enable binary logs on the MySQL nodes I work on. While binary logs may lead to more IO operations (writing binary logs make for more disk writes, obviously, but also disable some InnoDB optimizations), may consume more disk space (once I’ve worked with a company which had such a burst of traffic, that the binary logs to completely filled their disk in less than one day). If binary logs are enabled, the
--master-dataparameter allows for easy replication setup: the dump includes the
CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...statement, so no need to do stuff like
SHOW MASTER STATUSon the dumped node. Optionally, you can set
--master-data=2to have the statement commented.
--skip-lock-tables: When working with transactional-only storage engines (InnoDB is the most popular choice, but new engines are coming: Falcon, PBXT, Transactional-Maria, SolidDB and more), these parameters allow for a non-interruptive backup, which does not place read locks on all tables. It is possible to keep on reading and writing to the database while mysqldump is running with single transaction. Running in this mode does have its penalty: more IO operations (due to MVCC’s duplication of data while many transactions access the same data for Read/Write). The server is likely to perform more slowly during the dump time.
--default-character-set=utf8: I’ve seen so many MySQL installations in which world-wide textual data was stored in the Latin1 charset than I can remember. Many developers, who are testing using standard English data, are not even aware of the issues arrising from changing the data later on to utf8. But even those who are, are usually unaware of the necessity to configure the character set on a per connection basis, or for their specific clients (JDBC or PHP connectors, etc). mysqldump is no different, and if you have non-latin text in your tables, always remember to set this option.