Mysql Master-Slave synchronization configuration

Working with MySQL database configured master-slave synchronization, but do not know why synchronization failed. Working environment can not be operated casually, I built a master-slave synchronization environment in the local.

Backup tool is xtrabackup, installation and use of the tutorial see "Innobackupex implementation of MySQL database backup and recovery."

1. Database environment

Main Library (Master): 192.168.126.150

From library (Slave): 192.168.126.151

Database version: 5.5.32

2. Modify the My.cnf file

1) Modify master server master:

#vi/etc/my.cnf

[Mysqld]

Log-bin=/u01/app/mysql/log/mysql-bin #二进制日志必须启用

server-id=150//[must be] server unique ID, default is 1, usually take IP last paragraph

2) Modify the slave from the server:

#vi/etc/my.cnf

[Mysqld]

Log-bin=/u01/app/mysql/log/mysql-bin//[not required] enable binary logging

server-id=151//[must be] server unique ID, default is 1, usually take IP last paragraph

3. Restart the main library database/etc/init.d/mysql restart

4. Establish an account on the primary server and authorize slave:

#mysql-uroot-p

Mysql>grant REPLICATION SLAVE on * * to ' rep ' @ ' 192.168.126.151 ' identified by ' asdfg123 ';

5. Backing up the primary database

Innobackupex--defaults-file=/u01/app/mysql/my.cnf--user=root--password=*****--port=3306--socket=/u01/app/mysql /run/mysql.sock/u01/app/mysql/backup/

6. Copy the backup to the slave server

Tar czvf 2016-12-18_15-08-41.tar.gz 2016-12-18_15-08-41/

SCP 2016-12-18_15-08-41.tar.gz [Email protected]:/u01/app/mysql/backup/

7. Perform a restore on the standby (database off state, data folder is empty)

Innobackupex--defaults-file=/u01/app/mysql/my.cnf--user=root--password=hwj3509--use-memory=100m--apply-log/u01 /app/mysql/backup/2016-12-18_15-08-41

Innobackupex--defaults-file=/u01/app/mysql/my.cnf--user=root--password=hwj3509--copy-back/u01/app/mysql/backup /2016-12-18_15-08-41

#修改data文件夹权限

Chown-r Mysql:dba/u01/app/mysql/data

Start from Database

/etc/init.d/mysql start

8. View Xtrabackup_binlog_info Information

Cat/u01/app/mysql/backup/2016-12-18_15-08-41/xtrabackup_binlog_info

mysql-bin.000005460

9. Configure the slave from the server:

Mysql>change Master to master_host= ' 192.168.126.150 ', master_user= ' Mysync ', master_password= ' asdfg123 ',

Master_log_file= ' mysql-bin.000005 ', master_log_pos=460;

Mysql>start slave; To start the Copy from Server feature

#停止同步: Stop slave;

#重置同步: Reset slave;

10. Check the status of the replication function from the server:

#Slave_IO_Running和Slave_SQL_Running必须为Yes

Show Slave Status\g

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

Slave_io_state:waiting for Master to send event

master_host:192.168.126.150

Master_user:mysync

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000007

read_master_log_pos:277

relay_log_file:relaylog.000004

relay_log_pos:423

relay_master_log_file:mysql-bin.000007

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:277

relay_log_space:718

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:150

11. Verify Master-Slave synchronization

Perform additions and deletions on the main library to verify that the library is consistent with the main library.

This article is from the "three countries Cold jokes" blog, please be sure to keep this source http://myhwj.blog.51cto.com/9763975/1883754

Mysql Master-Slave synchronization configuration