MySQL master-slave synchronization, to realize automatic data synchronization

MySQL master-slave replication believe that had been used a lot of, but because of my job hasn't been used, relatively free during this period of time, has its own again, although the Internet has a lot of similar articles, but its implementation still worth recording,

Principle of master-slave synchronization

I/O thread to read on the primary server binlog logs, on the relay log file (relay - log)

Threads execute SQL native relay log file (relay - log) in the SQL statement, consistent with the primary server data

A Lord from:

Database server 192.168.4.51 configuration is given priority to the database server
Database server 192.168.4.52 configuration for from the database server
The client 192.168.4.50 test configuration

Configure the master server 192.168.4.51
Enable the binlog log

] # vim/etc/my CNF
[mysqld]
51//server_id server_id=
The log - bin=master51//log name
: wq
] # systemctl restart mysqld

The user authorization

User name custom, the client address use % or only from the server's address can be specified, you just to copy the data,

] # mysql - uroot -p password
Mysql> To grant the replication slave on *. * repluser @ "%" identified by "123: QQQ... A ";
Mysql> The quit;

Check the binlog log information

View the log file name and the position offset,

Mysql> Show master status \ G;

* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 row * * * * * * * * * * * * * * * * * * * * * * * * * * *

File: master51.000001//log name

Position: 441//offset

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in the set (0.00 SEC)



Configuration from the server 192.168.4.52

Specify server_id

Can customize Server_id values, but not the same as the primary server,

] # vim/etc/my CNF
[mysqld]
Server_id=52//server_id
: wq
] # systemctl restart mysqld//restart service

Ensure consistent with the primary data (if it is to use two new deployment database server configuration of master-slave synchronization, this operation can be ignored)

] # mysqldump - uroot -p password - master - data database name & gt;/allbak. SQL//on the master server backup data
] # SCP/allbak. SQL [email protected]:/root///copy the backup file to the server
Mysql> The create database database name;//in the main server from the server to create namesake database
] # mysql - uroot -p password database name & lt;/root/allbak. SQL//use the backup file to restore data from the server
] # vim/root/allbak. SQL//to examine the binlog in the backup file from the server log information
.
.
CHANGE the MASTER TO MASTER_LOG_FILE='master51.000001, MASTER_LOG_POS=441;//log name and offset

Specify the primary information

Database administrator root native login, specify the main server information, including log filename and offset allbak. SQL file records,

] # mysql - uroot -p/root/administrator password login the native
Mysql> Show slave status;//to check the status information, not just from the server
The Empty set (0.00 SEC)
Mysql> Change the master to//specifies the primary server
- & gt; Master_host="192.168.4.51",//the main server IP address
- & gt; Master_user="repluser",//the primary authorized user
- & gt; Master_password="123: QQQ... A ",//the primary authorized user password
- & gt; Master_log_file="master51 - bin. 000001",//the main server log file
- & gt; Master_log_pos=441;//the main server log offset
Mysql> Show slave status \ G;//to check the status information

* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 row * * * * * * * * * * * * * * * * * * * * * * * * * * *
Slave_IO_State: Waiting for the master to send event
Master_Host: 192.168.4.51//the main server IP address
Master_User: repluser
Master_Port: 3306

60 Connect_Retry:Master_Log_File: master51.000001
Read_Master_Log_Pos: 437
Relay_Log_File: 000002
host52relay - bin.Relay_Log_Pos: 604
Relay_Master_Log_File: master51.000001
Slave_IO_Running: Yes/Yes/IO thread state
Slave_SQL_Running: Yes/Yes/SQL thread state


The client test configuration
The master server to add access to the data connection user

Authorized user of all has the authority to add and delete data can

] # mysql - uroot -p password
Mysql> Grant the select, insert, update, and delete on *. * to admin @ "%" identified by "123: QQQ... A ";
Query OK, 0 rows affected, 1 warning (0.03 SEC)
Mysql> The quit

Client connection master server access data

50 hosts use the master server in 51 authorized user connection

] # mysql - h192.168.4.51 udamin - p123qqq... A
Mysql> Show grants;
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | Grants for admin @ %
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| GRANT the SELECT, INSERT, UPDATE, and DELETE ON *. * TO 'admin' @ '%' |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
1 row in the set (0.00 SEC)
Mysql> Insert into db3. User (name, uid) values (" lili ", 288);//db3 library and user tables are master-slave synchronous primary server has been before,
Query OK, 1 row affected (0.05 SEC)
Mysql> Insert into db3. User (name, uid) values (" lili ", 288);
Query OK, 1 row affected (0.28 SEC)
Mysql> Insert into db3. User (name, uid) values (" lili ", 288);
Query OK, 1 row affected (0.05 SEC)
Mysql> Select the name, uid from db3. User where name="lili";
+ -- -- -- -- -- - + -- -- -- -- -- - +
Uid | | name |
+ -- -- -- -- -- - + -- -- -- -- -- - +
288 | | lili |
288 | | lili |
288 | | lili |
+ -- -- -- -- -- - + -- -- -- -- -- - +
3 rows in the set (0.00 SEC)

Client connections from the server to access data

Client 50 using authorized user connections from the server host can see the same data and the primary server

] # mysql - h192.168.4.52 - udamin - p123qqq... A
Mysql> Select the name, uid from db3. User where name="lili";
+ -- -- -- -- -- - + -- -- -- -- -- - +
Uid | | name |
+ -- -- -- -- -- - + -- -- -- -- -- - +
288 | | lili |
288 | | lili |
288 | | lili |
+ -- -- -- -- -- - + -- -- -- -- -- - +
3 rows in the set (0.00 SEC)