MySQL master-slave setup detailed steps

First, the system environment: Centos 6.5

• MySQL version: mysql-5.5.25a
• master:192.168.4.9
• slave:192.168.4.10

Second, the principal and subordinate configuration needs attention points

• Master and Slave server operating system versions try to stay consistent
• Master and slave versions are consistent
• Slave Import of master backup data to complete
• Master has binary log turned on
• Slave turned on the trunk log
• Master and slave server_id values, unique

Third, master-slave configuration steps

Master configuration

• See if Server-id is unique
• Establish a master-slave authorized user
• Confirm that the binary log is open
• Exporting database data (backing up data)
• Transfer the backup data to Slave
• Master and slave server time consistent

Slave configuration

• See if Server-id is unique
• Import data from master export (restore Backup)
• Establish a master-slave relationship
• Slave and master server time are consistent

Iv. formal start of construction

Master configuration

1. Export Master Data

mysqldump-h192.168.4.9-uroot-p123456--quick--routines--master-data=2--single-transaction--all-databases > Da Ta.sql

The main thing here is --master-data=2 This parameter, otherwise can not find Pos and Binlog position when building master and slave

[Email protected] mysql]#/usr/local/mysql/bin/mysqldump-h192.168.4.9-uroot-p123456--quick--routines--master-dat a=2--single-transaction--all-databases > Data.sql

2, check server_id

[Email protected] mysql]# cat/data/mysql/my.cnf |grep Server-id

Server-id = 1

3, check whether the Binlog open

[email protected] mysql]# cat my.cnf |grep "Log-bin"

Log-bin=/data/mysql/mysql-bin

4. Transfer the backup to slave

Here use SCP, big data volume, should take other ways

[Email protected] ~]# scp/data/mysql/data.sql [email protected]:/data/

5. Create and authorize master and slave users

Grant Replication Slave on * * to [e-mail protected] '% ' identified by ' 123456 ';

Flush privileges;

Slave configuration

1. See if Server-id is unique

[Email protected] ~]# cat/data/mysql/my.cnf |grep Server-id

Server-id = 2

2. Backup and Restore

[Email protected] ~]#/usr/local/mysql/bin/mysql-uroot-h192.168.4.10-p </data/data.sql

Log in database to confirm data recovery integrity

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| MySQL |

| Performance_schema |

| Test |

| wordpress_01 |

+--------------------+

5 rows in Set (0.00 sec)

Check the data integrity, can be a simple test of some of the table's data is complete, I am looking at the number of rows

Mysql> Select COUNT (*) from Wp_usermeta;

+----------+

| COUNT (*) |

+----------+

| 16 |

+----------+

3, establish the master-slave relationship

First Look at POS number and Binlog location

[Email protected] ~]# Cat/data/data.sql |head-26

Change MASTER to master_log_file= ' mysql-bin.000016 ', master_log_pos=617; #主要是看这一行

Log in database to establish Master-slave

Change MASTER to master_log_file= ' mysql-bin.000002 ', master_log_pos=107, master_host= ' 192.168.4.9 ', master_port=3306 , master_user= ' Repl ', master_password= ' 123456 ';

Slave start; #启动从库

show slave status \g; view master and slave status

Mysql> show slave status \g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.4.9 #Master Server IP

Master_user:repl #Master created master-slave synchronization user

master_port:3306 #Master Server Port

Connect_retry:60

master_log_file:mysql-bin.000002 #Master Binlog

read_master_log_pos:107 #Master Log Pos number

relay_log_file:relaylog.000002 #中继日志

relay_log_pos:253 #中继日志pos号

relay_master_log_file:mysql-bin.000002 #中继日志文件

Slave_io_running:yes #Slave IO Status

Slave_sql_running:yes #Slave SQL Status

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:107 #执行主日志pos号

relay_log_space:402

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)

Read_master_log_pos and Exec_master_log_pos values can be assumed to be the master from synchronization success

Seconds_behind_master whether the master/slave is synchronizing, whether there is delay

master_log_file:mysql-bin.000002 Master Binlog is correct

Three items are no problem, can be certified as the main from the synchronization success

V. Problems in the construction of the master-slave process

• Ensure the integrity of your backup data
• Ensure backup on import, full import
• Data validation in earnest

Case

1, in the master-slave establishment is easy to produce 1062 and 1032, such an error, but does not affect the integrity of the data

solution:slave-skip-errors = 1062,1032

2, when the data import from the library, from the library will continue to produce binlog, so before importing data,

You can temporarily close the Binlog from the library, resolve the increase in the amount of data, or, when the synchronization is complete,

Cleanup of Binlog data.

Binlog Cleaning Method:

Purge master logs to ' mysql-bin.000022 '; # Delete mysql-bin.000022 before this log, according to the actual situation to determine the deletion

MySQL master-slave setup detailed steps