MySQL database master-slave synchronization, mysql database master-slave

MySQL database master-slave synchronization, mysql database master-slave

1. Preparations
Prepare two machines and install a MySQL database respectively. For example

10.254.141.23 as the primary database
10.254.166.60 as the slave Database

For MySQL database installation instructions, see MySQL database installation.
2. binlog is enabled for the master database.
Check whether binlog is enabled on the master database. This is required for master-slave synchronization.
Mysql> show master status;

The binlog file and the current location are displayed. This indicates that binglog is enabled. Otherwise, you need to enable this file first, modify/etc/my. cnf, and restart the database.
Add the following statements to the configuration file:
Log-bin = mysql-bin
Relay-log = relay-bin
Binlog_format = MIXED
Binlog_cache_size = 1 M
3. Create a synchronization account on the master database
Add an account on the host for synchronization from the slave Database
Mysql> GRANT REPLICATION SLAVE ON.To 'repl' @ '%' identified by '123 ';
Query OK, 0 rows affected (0.00 sec)
Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4. Back up data in the master database
If data already exists on the master server, you must first back up the data in the master database, import the data to the slave database at one time, and then synchronize the data from the master and slave databases. Otherwise, data conflicts may occur.
If the master-slave database is newly installed and no data is written, skip this step and configure the master-slave database directly.

4.1
Back up data in the master database
Mysql> set SQL _log_bin = 0;
[Root @ rs1 ~] #/Usr/local/bin/mysqldump-uroot-p123456-socket =/home/mysql. sock-all-databases-single-transaction-flush-logs-master-data = 2> db. SQL .2012-12-05
Mysql> set SQL _log_bin = 1;

4.2
Copy backup data to the slave server
[Root @ rs1 ~] # Scp/home/work/db. SQL .2012-12-05 10.136.51.111:/home/work

Synchronize data on the slave server:
Mysql> source/home/work/db. SQL .2012-12-05

5. Configure the slave Database
Head-30 db. SQL .2012-12-05
Find the rows starting with-change master to MASTER_LOG, as shown below:
-Change master to MASTER_LOG_FILE = 'mysql-bin.000002 ', MASTER_LOG_POS = 107;
Record the binary file used and the position where the statement is executed. Because master-slave synchronization needs to start from this position.

Then execute
Mysql> slave stop;
Mysql>
Change master
Master_host = '10. 254.141.23 ',
Master_user = 'repl ',
Master_password = '000000 ',
Master_port = 3306,
Master_log_file = 'mysql-bin.000007 ',
Master_log_pos = 107;
Mysql> slave start;

Check the slave database status.
Mysql> show slave status \ G;

Slave_IO_Running: Yes Slave_ SQL _Running: Yes Seconds_Behind_Master: 0 0 shows how many seconds the slave server is slower than the master server

Master-slave synchronization is completed.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.