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 main service data and synchronize the primary and standby initial data

Master

Export database information that needs to be synchronized from master
mysqldump-u***-p***--database Test > Test.sql
//Transfer backup information on master to slave
SCP/ Root/test.sql root@192.168.108.140:/opt/

Slave

Enter slave database
mysql-u***-p***
//Empty Test Database
drop DATABASE Test
//Import Master Test Database Information
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 is as follows, add the following configuration under [Mysqld]
#log-bin
server-id          =   1
log_bin            =   master-bin
expire_logs_days   =   ten
max_binlog_size    =   100M
binlog-do_db       =   Test
binlog_ignore_db   =   mysql
//restart MySQL database
service mysqld Restart
///If the installation is MARIADB can be restarted mariadb
systemctl restart

Slave

Modify the slave my.cnf file
vim/etc/my.cnf
//slave configuration is as follows, add the following configuration under [Mysqld]
Server-id         =   2
// Restart the MySQL database
service mysqld Restart
//If MARIADB is installed, mariadb systemctl Restart can be restarted Mariadb.service

A simple description of the parameter configuration, to ensure that the main standby Server-id unique. On master you need to open the binlog,log_bin=master_bin of MySQL and specify the name of the Binlog file.

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

Master

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

4. Get Master's Binlog location

Master

Enter MySQL database
mysql-u***-p***
//Set read lock
flush tables with read lock;
Get MySQL's Binlog file information and offsets 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. Set up the standby database

Enter the MySQL database
mysql-u***-p***
//Stop slave stops
slave;
Sets the Binlog information for 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 slave start
slave;

6. View the standby status

MARIADB [(none)]> show Slave status\g; 1. Row *************************** slave_io_state:waiting for master to send event Master_h ost:192.168.108.133 master_user:replication master_port:3306 Connec T_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_d B:replicate_do_table:replicate_ignore_table:replicate_wild_do_table:replicate_wild_ignore_ta ble:last_errno:0 last_error:skip_counter:0 Exec_maste r_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:0 Master_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:1 1 row in Set (0.00 sec) Error:no query specified

If: Slave_io_running:yes,slave_sql_running:yes is configured successfully, the configuration error repeats the above operation. If not, you can do this by viewing the MySQL log profiling process.

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, in master to change the table, field, data, slave will be synchronized changes.

write in the end: at that time want to try to use MySQL with the function of the database disaster preparedness, and later found that MySQL database master-slave synchronization will have some problems. The first thing that's not good for scripting is to ensure that the database initial information on both sides is the same 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 MySQL master-slave synchronization, only the database incremental synchronization, can not be a full amount of synchronization; and that if there is dirty data on the standby, a more data, when the main side of the new key to the same data, the synchronization failed. And then I'm going to try it. Can you script these operations and find that MySQL comes with a very restrictive synchronization function, and that there are too many things to intervene manually.

Url:http://www.cnblogs.com/jave1ove/p/6237324.html