MySQL5.6 Master-slave replication (Mysql Data synchronization configuration) _mysql

Planning

Master 192.168.56.2

Slave 192.168.56.5

1, in the main library, set up a copy of the use of the account Rep1, and granted replication slave permissions.

mysql> grant replication Slave on *.* to ' rep1 ' @ ' 192.168.56.2 ' identified by ' 123456 ';
Query OK, 0 rows affected (0.01 sec)

2, modify the main database server configuration file my.cnf, open Binlog, and set the Server-id value. The modification of these two parameters requires that the database service be restarted before it can take effect.

Vi/etc/my.cnf

[Mysqld]

Log-bin=/home/mysql/log/mysql-bin.log

Server-id=1

[root@rhel6 ~]# service MySQL restart
shutting down MySQL. [OK]
Starting MySQL. [OK]

3, in the main library, set read lock effective, this operation is to ensure that there is no database operations in order to obtain a consistent snapshot.

Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.02 sec)

4, gets the current binary log name and offset value on the main library. The purpose of this operation is to start data recovery from this point after the database is started.

 mysql> show master status; +-----------------+---------- +--------------+------------------+-------------------+ | file             | position | binlog_do_db | binlog_ignore_db |
 executed_gtid_set | +-----------------+----------+--------------+------------------+-------------------+ | mysqlbin.000021  |      120 |               |                   |             
      | +-----------------+----------+--------------+------------------+-------------------+ 1 row in  set  (0.03 sec) 

5, now the primary database server has stopped the update operation, need to generate a backup of the primary database, the way there are many ways to copy all the data files directly under the operating system to the database server, You can also export data by mysqldump or use the Ibbackup tool for backing up your database. If the primary database service can be stopped, a direct copy of the data file should be the quickest way to generate a snapshot:

[Root@rhel6 lib]# tar -zcvf mysql.tar.gz mysql mysql/tar: mysql/mysql.sock:  socket ignored mysql/mysqlbin.000019 mysql/test1/mysql/test1/db.opt Mysql/test1/pack. MyD mysql/test1/myisam2.frm mysql/test1/emp.ibd Mysql/test1/mer_myisam.
MRG mysql/test1/emp.frm mysql/test1/pri_t.trg ..... Upload from database [root@rhel6 lib]# scp mysql.tar.gz root@192.168.56.5:/root root@192.168.56.5 ' s  password:  mysql.tar.gz                                                                                                                                                                             100% 1402kb   1.4mb/s   00:00

6, the main database can be restored after the completion of the write operation, the remaining only to be performed from the library

mysql> unlock tables;
Query OK, 0 rows affected (0.02 sec)

7. Restore a consistent backup of the primary database to the database. If you are using a. tar.gz packaged package, just unpack it to the appropriate directory.

TAR-ZXVF mysql.tar.gz

8, modify the configuration file from the database my.cnf, add Server-id parameters. Note the value of the Server-id must be unique and cannot be the same as the primary database configuration, and if there are multiple from the database server, each must have its own unique Server-id value from the database server.

VI my.cnf

[Mysqld]

server-id=2

9, from the library, the use of the--skip-slave-start option to start the database, so that will not immediately start the replication process from the database service to facilitate our services from the database further configuration (optional)

Mysqld_safe--skip-slave-start &

or modify my.cnf, add skip-slave-start parameters, service MySQL start

10, from the database server to make the appropriate settings, specify the users of replication, the primary database server IP, port and the start of replication log files and locations, etc.

mysql> Change Master to
-> master_host= ' 192.168.56.2 ',-> master_user=
' rep1 ',
-> m  aster_port=3306,
-> master_password= ' 123456 ',-> master_log_file=
' mysqlbin.000021 ',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

11, in from the library, start the slave thread

mysql> start slave;
Query OK, 0 rows affected (0.17 sec)

12, view the process from the library

mysql> show processlist \g;  1. row ***************************      id:  1    user: root    host: localhost      db :  zx command: query    time: 0   state: init    Info:  show processlist *************************** 2. row ***************************  
    id: 4    user: system user    Host:       db: null Command: connect    time: 1484    State: waiting for master to send event    info: null ********
 3. row ***************************      id: 5    user: system user    Host:       db: null Command: connect    Time:  739   State: Slave has read all relay log; waiting for  The slave i/o thread to update it    info: null 3 rows  in set  (0.00 sec) mysql> show slave status \g **********************  1. row ***************************                 Slave_IO_State: Waiting for master to send  Event                    master_host: 192.168.56.2                    master_user: rep1                    master_port: 3306                  connect_retry: 60                master_log_file: mysqlbin.000021            read_master_log_pos: 652                 relay_log_file: rhel6-relay-bin.000002                  Relay_Log_Pos:  814         relay_master_log_file: mysqlbin.000021               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: 652                relay_log_space: 987               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                    MASTER_UUID: 3743271B-AA6D-11E6-AA2E-080027E5F5DD               master_info_file: /mysqldata/master.info        &nBsp;             sql_delay: 0            sql_remaining_delay: null        slave_sql_running_state: slave has read all relay log; waiting  for the slave i/o thread to update it             master_retry_count: 86400                    Master_Bind:         Last_IO_Error_Timestamp:       Last_SQL_Error_Timestamp:   
              Master_SSL_Crl:             Master_SSL_Crlpath:              retrieved_gtid_set:               Executed_Gtid_Set:                   auto_position: 0 1 row in set  (0.00 SEC)

Slave_io_running and slave_sql_running All are yes to build success. Check the cause of the error if it goes wrong. The two errors I encountered when I built:

(1) Last_io_error:fatal error:the slave I/O thread stops because master and slave have MySQL server equal; These uuids must are different for replication to work.

Cause: The main library of the entire directory to copy over, the main library and from the library UUID consistent, modify the UUID from the library can be.

Methods: Modify the $DATADIR/AUTO.CNF, in accordance with the 16 into the format to change, restart MySQL can be.

(2) Last_io_error:got fatal Error 1236 from master when reading data from binary log: ' could ' Binary Log index file '

Reason: Configure Master_log_file configuration Error when change master is configured

Method: Change Master to master_log_file= ' mysqlbin.000021 '; start slave;

13, test the correctness of replication

Main Library mysql> create database zx;
query ok, 1 row affected  (0.05 sec) mysql> use zx;
Database changed mysql> create table t (Id int); query ok, 0 rows affected  (0.09 sec) mysql> insert into t 
VALUES (1), (2), (3); query ok, 3 rows affected  (0.01 sec) records: 3  duplicates: 
0  warnings: 0 from the library mysql> show databases;
+--------------------+ | database           | +--------------------+ | information_schema | | data                | | mysql               | | performance_schema | | test                | | test1               | | zx             
    | +--------------------+ 7 rows in set  (0.00 sec) mysql> use zx reading  Table information for completion of table and column names You can

 turn off this feature to get a quicker startup with -a
Database changed mysql> select * from t;
+------+ | id   |
+------+ |    1 | |    2 | |    3 | +------+ 3 rows in set  (0.00 sec)