Mysql database master-slave synchronization, mysql database master-slave

Mysql database master-slave synchronization, mysql database master-slave

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 master service data and synchronize the initial data of the master and slave nodes.

Master:

// Export the database information to be synchronized from the master. mysqldump-u ***-p *** -- database test> test. SQL // transmit the backup information on the master to the slave scp/root/test. SQL root@192.168.108.140:/opt/

Slave:

// Enter the slave database mysql-u ***-p *** // clear the test database drop database test // import the test database information of the master database source/opt/test. SQL

2. Configure the mysql database on the master and slave.

Master:

// Modify my. cnf file vim/etc/my. the cnf // master configuration is 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 can restart mariadbsystemctl restart mariadb. service

Slave:

// Modify my. cnf file vim/etc/my. cnf // The slave configuration is as follows, add the following configuration under [mysqld]: server-id = 2 // restart mysql Database service mysqld restart // If mariadb is installed, restart mariadbsystemctl restart mariadb. service

A brief description of the parameter configuration ensures that the active/Standby server-id is unique. Binlog, log_bin = master_bin of mysql must be enabled on the master to specify the name of the binlog file.

3. Create a replication user with the replication slave permission to ensure that slave can synchronize master data

Master:

grant replication slave on *.* to 'replication'@'192.168.108.140' identified by 'replication';

4. Obtain the binlog location of the master

Master:

// Enter the mysql database mysql-u *****-p **** // set the read lock flush tables with read lock; // obtain the binlog file information and offset of mysql to show master status; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | metric | + metric + ---------- + -------------- + metric + | master-bin.000010 | 3713 | test | mysql | + metric + ---------- + -------------- + ------------------ + 1 row in set (0.00 sec) // unlock tables;

5. Set the backup database

// Enter mysql database mysql-u *****-p ***** // stop slavestop slave; // set the binlog information of the corresponding master, MariaDB [(none)]> change master to-> master_host = '2017. 168.108.133 ',-> master_user = 'replicase',-> master_password = 'replicase',-> 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_Host: 192.168.108.133                  Master_User: replication                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: master-bin.000010          Read_Master_Log_Pos: 3881               Relay_Log_File: mariadb-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:           Replicate_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:        Seconds_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 row in set (0.00 sec)ERROR: No query specified

If: Slave_IO_Running: Yes, Slave_ SQL _Running: Yes, the configuration is successful. If the configuration error is correct, repeat the preceding operation. If the problem cannot be solved, you can view the mysql Log analysis process.

vim /var/log/mariadb/mariadb.log

7. Test. In fact, the test is not easy to write. After the configuration is successful, the Server Load balancer directly connects to the master/slave database and changes the tables, fields, and data on the master database. The Server Load balancer will synchronize the changes.

Conclusion:At that time, I tried to test whether I could use the functions provided by mysql for database disaster recovery. Later I found that there were some problems with the master-slave synchronization of the mysql database. The first thing that is not scripted is to ensure that the initial information of the databases on both sides is the same before synchronization, because the mysql-binlog location configured on the slave end is only the location of the current master database information, data before this location can only be manually imported. The second is that during mysql master-slave synchronization, only incremental synchronization of the database is allowed, and full synchronization is not allowed. In addition, if dirty data occurs on the slave end, an additional row of data is added, synchronization fails when a data entry with the same primary key is added to the primary key. After that, I will try to script these operations and find that the synchronization function of mysql is very restrictive and there are too many manual interventions.