MySQL Database master-Slave synchronization

Environment:

mater:centos7.1 5.5.52-mariadb 192.168.108.133

slave:centos7.1 5.5.52-mariadb 192.168.108.140

1. Export the primary service data and synchronize the primary and standby data

Master

Export the database information that needs to be synchronized from master mysqldump-u***-p***--database Test > test.sql//Transfers the backup information on master to slave scp/root/test.sql [ Email protected]:/opt/

Slave

Enter the slave database mysql-u***-p***//empty the test database drop databases test//Import the test database information for Master Source/opt/test.sql

2. Configure the MySQL database on master and slave

Master

Modify Master's my.cnf file Vim/etc/my.cnf//master configuration as follows, add the following configuration under [mysqld] #log-binserver-id          =   1log_bin            =   Master-binexpire_logs_days   =   10max_binlog_size    =   100mbinlog-do_db       =   Testbinlog_ignore _db   =   mysql//restart MySQL database service mysqld restart//If the installation is mariadb you can restart Mariadbsystemctl restart Mariadb.service

Slave

Modify the slave my.cnf file Vim/etc/my.cnf//slave configuration as follows, add the following configuration under [mysqld] server-id         =   2//restart MySQL database service mysqld restart//If the installation is mariadb you can restart Mariadbsystemctl restart Mariadb.service

Simply explain the configuration of the parameters to ensure that the primary and standby Server-id unique. On master, you need to turn on MySQL Binlog,log_bin=master_bin, specifying the name of the Binlog file.

3. Create a replication user with replication slave permissions to ensure that the slave can synchronize the master's data in the past

Master

Grant Replication Slave on * * to ' replication ' @ ' 192.168.108.140 ' identified by ' replication ';

4. Get the Binlog location of master

Master

Enter MySQL database mysql-u***-p***//set read lock flush tables with read lock;//get MySQL binlog file information and offset show Master status;+----------- --------+----------+--------------+------------------+| File              | Position | binlog_do_db | binlog_ignore_db |+-------------------+----------+--------------+------------------+| master-bin.000010 |     3713 | Test         | MySQL            |+-------------------+----------+--------------+------------------+1 row in Set (0.00 sec)// unlock unlock tables;

5. Setting up the Standby database

Enter MySQL database mysql-u***-p***//stop slavestop slave;//set the Binlog information corresponding to the Master mariadb [(none)]> change Master to    Master_host= ' 192.168.108.133 ',    master_user= ' replication ',    master_password= ' replication ',    -master_log_file= ' master-bin.000010 ',    master_log_pos=3713;//start slavestart slave;

6. View the Backup status

MariaDB [(none)]> show slave status\g;*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos t:192.168.108.133 master_user:replication master_port:3306 Connect_ret Ry:60 master_log_file:master-bin.000010 read_master_log_pos:3881 Relay_log_file:ma riadb-relay-bin.000002 relay_log_pos:698 relay_master_log_file:master-bin.000010 Slave _io_running:yes Slave_sql_running:yes Replicate_do_db:replicate_ignore_db:r                   Eplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:              last_errno:0 last_error:skip_counter:0 exec_master_log_pos:3881            relay_log_space:994 Until_condition:none   until_log_file:until_log_pos:0 Master_ssl_allowed:no Master_ssl_ca_file: Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Sec               Onds_behind_master:0master_ssl_verify_server_cert:no last_io_errno:0 Last_io_error: last_sql_errno:0 last_sql_error:replicate_ignore_server_ids:master_server_id:11 RO W In Set (0.00 sec) Error:no query specified

If: Slave_io_running:yes,slave_sql_running:yes is the configuration succeeds, the configuration error repeats above operation. If not resolved, you can view the MySQL log parsing processing.

Vim/var/log/mariadb/mariadb.log

7. Test . In fact, the test is not good to write, after the successful configuration directly connected to the master-slave database, change the table, field, data on master, slave will change synchronously.

written in the end: when I wanted to try to do with the MySQL feature to do database recovery, and later found that MySQL database master-slave synchronization will have some problems. The first thing that is not good for scripting is to ensure that the initial database information is the same on both sides before synchronizing, because the Mysql-binlog location of the standby configuration is only the location of the current primary database information, and the data before that location can only be imported manually. The second is the MySQL master-slave synchronization, only the incremental synchronization of the database, not full-volume synchronization, and if there is dirty data on the standby, one more data, when the main side of a new primary key to the same data, the synchronization failed. Then I'll try to script these operations and find that MySQL comes with a very restrictive sync feature, and there's too much manual intervention.

MySQL Database master-Slave synchronization