Linux MySQL master-slave Replication (Replication) (MySQL Data Synchronization) Configuration

This article will share with you a good tutorial on configuring Linux MySQL master-slave Replication (Replication) (MySQL Data Synchronization). Thank you for your reference.

MySQL is an open-source relational database system. Replication is a process that replicates data from a MySQL database server (master server) to another server (slave server slave.

Configure the master server)
1. Edit the database configuration file my. cnf, which is usually in the/etc/directory.

1. # vi/etc/my. cnf
Add the following code under [mysqld:

1. log-bin = mysql-bin
2. server-id = 1
3. innodb_flush_log_at_trx_commit = 1
4. sync_binlog = 1
5. binlog-do-db = wordpress
6. binlog_ignore_db = mysql
1 In server-id = 1 can be defined, as long as it is unique.
Binlog-do-db = wordpress indicates that only wordpress is backed up.
Binlog_ignore_db = mysql indicates that the backup mysql is ignored.
If binlog-do-db and binlog_ignore_db are not added, all databases are backed up.
2. Restart MySQL:

1. # service mysqld restart
3. log on to the MySQL server.

1. # mysql-uroot-p
Create a new user on the master server to grant the "replication slave" permission. You do not need to grant other permissions. In the following command, replace X. X with the IP address of the slave server.

1. mysql> create user 'user' @ 'x. X. X. X' identified by 'Password ';
2. mysql> grant replication slave on *. * TO 'user' @ 'x. X. X. X' identified by 'Password ';
4. Run the following command to lock the database to prevent data writing.

1. mysql> flush tables with read lock;
In this case, do not exit the mysql command line, because after exiting the command line, the global table lock becomes invalid. In a new window, continue to execute the following command.
5. Export the database

1. # mysqldump-u root-p123456 -- all-databases>/root/all. SQL
6. Use the scp command to transmit the database file all. SQL to the slave server.

1. # scp/root/all. root@www.example.com:/root
7. Connect to the database again and go to the mysql command line to check the master status.

1. mysql> show master status;
Please note the information that is displayed, which will be used by the configuration slave server.
+ ------ + ---- + ----- + ------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------ + ---- + ----- + ------ +
| Mysql-bin.000003 | 1001741 | dbispconfig | mysql |
+ ------ + ---- + ----- + ------ +
1 row in set (0.00 sec)
8. Unlock the data table.

1. mysql> unlock tables;
Configure slave server (slave)
Log on to the slave server.
1. Import the database of the master server.

1. # mysql-u root-p123456 </root/all. SQL
2. Edit the configuration file my. cnf and add it under [mysqld:

1. server-id = 2
2. You can define it as long as it is unique.
3. Save the file and restart mysqld.

1. # service mysqld restart
4. log on to the mysql server and run the following command.

1. mysql> CHANGE MASTER
2. MASTER_HOST = 'x. X. X. x ',
3. MASTER_USER = 'user ',
4. MASTER_PASSWORD = 'Password ',
5. MASTER_PORT = 3306,
6. MASTER_LOG_FILE = 'mysql-bin.000001 ',
7. MASTER_LOG_POS = 98,
8. MASTER_CONNECT_RETRY = 10;
MASTER_HOST: IP address of the master server.
MASTER_USER: username created when configuring the master server
MASTER_PASSWORD: User Password
MASTER_PORT: the mysql port of the master server. If it has not been modified, the default value is enough.
5. Start the slave process.

1. mysql> start slave;
6. view the mysql log. Generally, it is in the/var/log/directory. If the startup is successful, you should see a log similar to the following.
091104 8:42:02 [Note] Slave I/O thread: connected to master 'root @ X. X: 3306 ?, Replication started in log 'mysql-bin.000001? At position 98
Now the master server and slave server are configured. In addition, you may need to set the expiration time of the database binary log of the master server. You can use the expire_logs_days parameter in the configuration file to set the expiration time.

Check for normal operation
1. Execute show master status on the MASTER server. If the STATUS has been recorded, the Position should be non-zero. If no record exists, confirm that the master server is running with the log-bin and server-id options.
2. Run showshow slave status on the SLAVE server to check whether the values of slave_IO_Running and slave_ SQL _Running are Yes. If not, verify the option used when starting the slave server.
3. If the slave server is running, is the connection established with the master server? Use show processlist to find the I/O and SQL threads and check their State columns to see how they are displayed. If the I/O thread status is ing to master, verify whether the master server is running by copying the user's permissions, master server host name, and DNS settings on the master server, and whether it can be accessed from the slave server.

Notes
1. the time zone of the master server and slave server must be the same; otherwise, mysql execution of time-related functions will cause data inconsistency.