MySQL master-slave synchronization configuration in Linux and linuxmysql master-slave Synchronization

MySQL master-slave synchronization configuration in Linux and linuxmysql master-slave Synchronization

Centos6.5 MySQL master-slave synchronization MySQL version 5.6.25

Master server: centos6.5 IP: 192.168.1.101

Slave server: centos6.5 IP: 192.168.1.102

1. master server configuration

1. Create a synchronization account and specify the server address

[Root @ localhost ~] Mysql-uroot-pmysql> use mysqlmysql> grant replication slave on *. * to 'testuser' @ '192. 168.1.102 'identified by '20140901'; mysql> flush privileges # refresh Permissions

The authorized user testuser can only access the database of the master server 192.168.1.101 from the address 192.168.1.102 and only have the database backup permission.

2. Modify the/etc/my. cnf configuration file vi/etc/my. cnf.

Add the following parameters under [mysqld]. If the file already exists, you do not need to add server-id = 1 log-bin = mysql-bin # Start the MySQL binary log system, binlog-do-db = ourneeddb # binlog-ignore-db = mysql # the database to be synchronized is not synchronized to the mysql System database. If there are other databases that do not want to be synchronized, add [root @ localhost ~] /Etc/init. d/mysqld restart # restart the service

3. view the master Status of the master server (note the File and Position items. The slave server requires these two parameters)

mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000012 |      120 | ourneeddb| mysql            |+------------------+----------+--------------+------------------+

4. Export the database

Lock the database before exporting the database

Flush tables with read lock; # Database read-only lock command to prevent data writing during Database Export

Unlock tables; # unlock

Export the database structure and data: mysqldump-uroot-p ourneeddb>/home/ourneeddb. SQL

Export the stored procedure and function: mysqldump-uroot-p-ntd-R ourneeddb> ourneeddb_func. SQL

Tips:-ntd export stored procedure,-R export Function

 

Ii. slave server configuration

1. Modify the/etc/my. cnf configuration file vi/etc/my. cnf.

Add the following parameters under [mysqld]. If the file already exists, you do not need to add server-id = 2 # Set the slave server id, different log-bin = mysql-bin # Start the MySQ binary log system replicate-do-db = ourneeddb # Name of the database to be synchronized replicate-ignore-db = mysql # No synchronize the mysql System database [root @ localhost ~ ]/Etc/init. d/mysqld restart # restart the service

2. Import Database

The import process is not described here

3. Configure master-slave Synchronization

[Root @ localhost ~ ] Mysql-uroot-pmysql> use mysql> stop slave; mysql> change master to master_host = '2017. 168.1.101 ', master_user = 'testuser', master_password = '000000', master_log_file = 'mysql-bin.000012', master_log_pos = 12345678; # log_file and log_pos are File and Positionmysql> start slave in master state of the master server; mysql> show slave status \ G;

 

* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: testuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 120
Relay_Log_File: orange-2-relay-bin.000003
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: orange
Replicate_Ignore_DB: mysql, test, information_schema, cece_schema
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: 120
Relay_Log_Space: 1320
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: 773d2987-6821-11e6-b9e0-00163f0004f9
Master_Info_File:/home/mysql/master.info
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

Note: View Slave_IO_Running: Yes Slave_ SQL _Running: Yes, which must be Yes and Log_File and Log_Pos must be in the master state File, with the same Position

If both are correct, the configuration is successful!