Mysql master-slave replication practices

Mysql master-slave replication practices
Asynchronous master-slave Replication

 Master-slave deployment steps:

• Backup and Restoration
• • Use mysqldump or xtrabackup
  • Restore the existing basic data of the master database to the slave Database
• Authorization
• • Grant replication slave on *.*
  • An account for copying binlog to the slave Database
• Configure replication and start
• • Configure replication information from the database and point to the master
• View master-slave replication Information
• • Show slave status\ G

1) backup and recovery master: 101 slave: 100a) master database backup

Aiapple @ ubuntu :~ $ Mysqldump-uroot-p -- socket =/tmp/mysqldata/node1/mysql. sock -- master-data -- all-databases> all_master. SQL -- master-date: record the binlog location during Backup aiapple @ ubuntu :~ $ Cat all_master. SQL | less ---- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000001 ', MASTER_LOG_POS = 6125; ---- Current Database: 'db1 '--

 

B) restore the master database from the slave database to remotely connect to the slave database, and use source to restore the slave database to add the master database whitelist: mysql> grant all on *. * to root@192.168.1.101 with grant option; set password: mysql> set password for root @ 'localhost' = password ('000000'); Query OK, 0 rows affected (000000 sec) add the slave database whitelist to the master database:

mysql> grant all on *.* to root@192.168.1.100 identified by '000000' with grant option;

 

Log on to the slave database of the master database and restore the data:

aiapple@ubuntu:~$ mysql -uroot -p -h 192.168.1.100 mysql> source all_master.sql

 

2) authorize replication slave

mysql> grant replication slave on *.* to repl@192.168.1.100 identified by 'repl';Query OK, 0 rows affected (0.00 sec)

3) slave Database Configuration Replication

# View help information? Change master to mysql> change master to MASTER_USER = 'repl'; Query OK, 0 rows affected, 2 warnings (0.03 sec) mysql> change master to MASTER_PASSWORD = 'repl '; query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> change master to MASTER_HOST = '2017. 168.1.101 '; Query OK, 0 rows affected (0.03 sec) mysql> change master to MASTER_LOG_FILE = 'mysql-bin.000001 ';

4) start:

Mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status \ G; slave_io_running: yesslave_ SQL _running: yes # mysql> show processlist; + ---- + ------------- + ----------- + ------ + --------- + ------ + upper + ------------------ + | Id | User | Host | db | Command | Time | State | Info | + ---- + ------------- + ----------- + ------ + --------- + ------ + bytes + ---------------- + | 2 | root | localhost | NULL | Query | 0 | init | show processlist | 16 | system user | NULL | Connect | 60 | Waiting for master to send event | NULL | 17 | system user | NULL | Connect | 60 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | + ---- + ------------- + ----------- + ------ + --------- + ------ + accept + ------------------ +

Note:

• Master/Slave server_id should be different;
• Enable binlog for Master/Slave
• MASTER_LOG_FILE specifies the first file of bin_log in the master database;

  Semi-synchronous Replication Configure mysql semi-synchronous replication semi-sync to check which plug-ins are available

show plugin

1. Set up master-slave asynchronous replication

1) the master database is fully standby, And the slave database recovers mysqldump-uroot-p123456 -- socket =/data/mysql/node1/mysqld. sock -- single-transaction-A -- master-data = 1> all_db.sqlmysql-utest-ptest-h (slave database IP)-P3306mysql> source all_db. SQL; 2) master database Authorization User grant replication slave on *. * to repl @ '(slave database IP address) 'identified by 'repl'; 3) copy less all_db. SQL | grep "change master to" change master to master_host = '(master database IP)', master_user = 'repl', master_password = 'repl ', master_log_file = 'xxx', master_log_pos = XXX; start stave; show slave status \ G4) Copy check master database: use db1; insert into t1 values (10); slave Database: use db1; select * from t1; (obtain data) master database: drop database db2; slave database: show databases; (display db2 deleted) 5) view thread master database: show processlist; (dump thread) slave Database: show processlist; (IO thread, SQL thread) 6) view log slave Database: cd/data/mysql/node1cat master. infocat relay-log.info

2. Installation of master-slave semi-sync Replication

1) install the ins show plugins in the master database; install PLUGIN rpl_semi_sync_master SONAME 'semisync _ master. so '; 2) INSTALL the plug-in show plugins from the library; install plugin rpl_semi_sync_slave SONAME 'semisync _ slave. so '; 3) parameter settings master database: show variables like' % semi % '; set global rpl_semi_sync_master_enabled = 1; slave Database: set global rpl_semi_sync_slave_enabled = 1; 4) restart Master/slave replication slave Database: stop slave; start slave; 5) Check the status of show global status like '% semi %'; 6) Check the master database for replication: use db1; insert into t1 values (100); slave Database: use db1; select * from t1; (obtain data) 7) Test latency slave Database: stop slave; master database: use db1; insert into t1 values (1); (card 10 s) set global rpl_semi_sync_master_timeout = 1000; (set master and other slave time 1 second) slave Database: start slave; stop slave; master database: use db1; insert into t1 values (88); (Card 1 s)

 

Note: The waiting time of the primary database of rpl_semi_sync_master_timeout cannot be set to a large value; otherwise, the primary database avalanche effect may occur; it is best to set the waiting time to within 1 second;

 

Parallel Replication 1. MySQL parallel replication

# Slave Database: show variables like '% slave_par %'; set global slave_parallel_workers = 10; set the number of SQL threads to 10 # restart slavestop slave; start slave; # view the thread show processlist; (10 worker threads)

Note: There are actually 11 threads, 10 worker threads, and 1 scheduling thread; Partial data replication  Partial data replication: In the configuration file, set the master database to add parameters specified to the database: binlog_do_db = db1binlog_ignore_db = db1binlog_ignore_db = db2 or add parameters from the database to the table; replicate_do_db = db1replicate_ignore_db = db1replicate_do_table = db1.t1replicate _ wild_do_table = db %. % # configure replicate_wild_ignore_table = db1. % #1. Note: We recommend that you add parameters to the slave database so that you can specify parameters at the table level more flexibly.

1) master database: create database db2; 2) Add configuration # vim/data/mysql/my1.cnf (replicate_do_db = db2) to copy the configuration file from the database) restart mysql # mysqladmin-uroot -- socket = XXX -- port = 3306-p123456 shutdown #/usr/local/mysql/bin/mysqld_safe -- defaults-file =/data/mysql/my1.cnf & show slave status; display: replicate_do_db = db2 3) test master database: use db1; delete from t1; slave Database: use db1; select * from t1; (retain data) master database: use db2; create table user (a int, B int); slave Database: use db2; show tables; (view the user table)

  Cascade Replication  Cascade replication (A-> B-> C)

1) slave Database: # vim/data/mysql/my1.cnf (log_slave_updates) # mysqladmin-uroot -- socket = XXX -- port = 3306-p123456 shutdown #/usr/local/mysql/bin/mysqld_safe -- defaults-file =/data/mysql/my1.cnf & 2) create a new instance and create a slave database 2 instance on the master database server # mysqladmin-uroot -- socket = XXX -- port = 3306-p123456 shutdown # kill-9 (mysqld_safe process no) # cp-r node1 node2 # vim my. cnf (modify related parameters, port 3307) # chown-R mysql. mysql node2 #/usr/local/mysql56/bin/mysqld_safe -- defaults-file =/data/mysql/my1.cnf & #/usr/local/mysql56/bin/mysqld_safe -- defaults-file =/data/mysql/my2.cnf & # mysqldump-utest-ptest-hXXX-P3306-A -- master-data = 1> d731. SQL (dump slave database 1 full backup) # mysql-uroot -- socket =/data/mysql/node2/mysqld. sock-p123456 <d731.sql3) Configure master-slave authorization from 1 and slave 2: grant replication slave on *. * to repl @ '(from 2IP) 'identified by 'repl'; replication from 2 configuration: less d731. SQL | grep "change master to" change master to master_host =' (from 1IP) ', master_user = 'repl', master_password = 'repl', master_log_file = 'xxx', master_log_pos = XXX; start stave; show slave status \ Gshow processlist; 4) cascade replication test master database: create database db3; From 1: show databases; (obtain new database) from 2: show databases; (obtain new database)

 

  Monitoring and Processing  

# Viewing Status
Show slave status;

 

Success or failure: slave_ SQL _running: slave_io_running: How long is the delay seconds_Behind_Master? when there is a problem with the slave database, the error handling is common: 1062 (primary key conflict), 1032 (record does not exist) solved:

• Manual processing
• Replication skipping error: set global SQL _slave_skip_counter = 1

Replication errors are mostly caused by inconsistent Master/Slave Data. The best way is to verify Master/Slave Data Replication. percona has a Master/Slave replication verification tool;