MySQL master-slave Replication Practice

Asynchronous Master-slave replication  Master-Slave Deployment steps:

• Backup restore
• • Use mysqldump or Xtrabackup
  • Restore existing base data from the main library to the library
• Authorized
• • Grant Replication Slave on * *
  • A copy of the Binlog account from the library
• Configure replication, and start
• • Configure replication information from the library and point to the master
• View Master-slave replication information
• • Show slave status \g

1) Backup Restore Master: 101 from: 100a) Main Library backup

[Email protected]:~$ mysqldump-uroot-p--socket=/tmp/mysqldata/node1/mysql.sock--master-data--all-databases  > all_master.sql--master-date: Binlog location when recording backups [email protected]:catless ----Position to start replication or point-in – time recoveryfrom – Change MASTER to Master_log_file='mysql-bin.000001', master_log_pos=6125  ----Current Database: ' DB1 '--

b) remotely connect from the library to the primary library, use source restore to add the main library from the library whitelist:mysql> grant all on *. * to [email protected] with Grant OPTION; Set password:mysql> set password for [email protected] ' localhost ' =password (' 000000 '); Query OK, 0 rows Affected (0.00 sec) Main Library Add a whitelist from the library:

Mysql> Grant All on * * to [email protected]192.168. 1.100 ' 000000 ' with GRANT option;

The main library logs in from the library and restores the data:

192.168. 1.100 MySQL> Source all_master.sql

2) Empowering Replication slave

mysql> grant replication Slave on * * to [email protected]192.168. 1.100 ' Repl '  0 rows affected (0.00 sec)

3) Copy from library configuration

#查看帮助信息? Change Master to MySQL> Change Master to master_user='Repl'; Query OK,0Rows affected,2Warnings (0.03sec) MySQL> Change Master to master_password='Repl'; Query OK,0Rows affected,2Warnings (0.02sec) MySQL> Change Master to master_host='192.168.1.101'; Query OK,0Rows Affected (0.03sec) MySQL> Change Master to master_log_file='mysql-bin.000001';

4) Start:

Mysql>start slave; Query OK,0Rows Affected (0.01sec) MySQL>show slave status\g; slave_io_running:yesslave_sql_running:yes# indicates successful MySQL configuration>show Processlist;+----+-------------+-----------+------+---------+------+------------------------------------------------------- ----------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-------------+-----------+------+---------+------+------------------------------------------------ -----------------------------+------------------+|2| Root | localhost | NULL | Query |0| init | Show Processlist | | -|           System user | | NULL | Connect | -| Waiting forMaster to send Event | NULL | | -|           System user | | NULL | Connect | -| Slave have read all relay log; Waiting forThe slave I/O thread to update it | NULL |+----+-------------+-----------+------+---------+------+------------------------------------------------ -----------------------------+------------------+

Attention:

• The master-slave server_id should be different;
• Master-Slave Open Binlog Log
• MASTER_LOG_FILE Specifies the first file of the main library bin_log;

semi-synchronous replication Configure MySQL semi-synchronous replication Semi-sync see which plugins are available

Show Plugin

1. master-Slave asynchronous copy construction

1The main library is fully prepared, and the repository recovery mysqldump-uroot-p123456--socket=/data/mysql/node1/mysqld.sock--single-transaction-a--master-data=1>All_db.sqlmysql-utest-ptest-h (from library IP)-P3306mysql>source All_db.sql;2) Main Library authorized user grant replication slave on*. * to [email protected]'(from library IP)'Identified by'Repl';3) Copy from library configuration Lessall_db.sql|grep "Change Master to"Change master to Master_host='(Main library IP)', master_user='Repl', master_password='Repl', master_log_file='XXX', master_log_pos=xxx;start stave;show slave status\g4Copy Inspection Main Library: use Db1;insert into T1 values (Ten); From the library: use DB1;Select*from T1; (get data) Main Library: Drop database DB2; from library: show databases; (show DB2 deleted)5View line Cheng: show processlist; (dump thread) from library: show processlist; (io thread, SQL thread)6) view logs from library: CD/data/mysql/Node1CatMaster.InfoCatRelay-log.Info

2. master-Slave semi-synchronous copy Installation

1) Main Library install plugin show plugins;InstallPLUGIN Rpl_semi_sync_master SONAME'semisync_master.so';2) install plugin from library show Plugins;install PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';3) parameter Settings Main library: show variables like'%semi%'; SET GLOBAL rpl_semi_sync_master_enabled=1; from library: SET GLOBAL rpl_semi_sync_slave_enabled=1;4) Restart master-slave copy from library: Stop Slave;start slave;5) status check show global status like'%semi%';6) Copy Check Main library: use Db1;insert into T1 values ( -); From the library: use DB1;Select*from T1; (Get data)7test delay from library: stop slave; main Library: Use Db1;insert to T1 values (1);(Card 10s) set global Rpl_semi_sync_master_timeout= +(set master etc from time 1 seconds) from library: Start slave;stop slave; Main Library: Use Db1;insert into T1 values ( the);(be card 1s)

Note: rpl_semi_sync_master_timeout Main Library Wait time cannot be set large, otherwise it will cause the main library avalanche effect; preferably in 1 seconds;

Parallel Replication 1.MySQL Parallel replication

' %slave_par% ' Set global Slave_parallel_workers=ten; Sets the number of SQL threads 10# restart slavestop slave;start slave; #查看线程show Processlist; (10 worker threads)

Note: There are actually 11 threads, 10worker threads, 1 scheduler threads; Partial data replication  Partial data replication: Set the main library in the configuration file add parameters to the library: BINLOG_DO_DB=DB1BINLOG_IGNORE_DB=DB1BINLOG_IGNORE_DB=DB2 or add parameters from the library can be to the table; replicate_do_db= db1replicate_ignore_db=db1replicate_do_table=db1.t1replicate_wild_do_table=db%.% #配置设置replicate_wild_ignore_ table=db1.% #1打头的表 Note: It is recommended to use the add parameter from the library, so that more flexibility can be specified to the table level; partial replication

1) Main Library: Create database DB2;2) Add configuration #vim from the library section replication configuration profile/DATA/MYSQL/MY1.CNF (replicate_do_db=DB2) 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=DB23test the Main library: use db1;delete from T1; library: use DB1;Select*from T1; Main Library: Use db2;create table User (aintBint); From library: Use db2;show tables; (view to User table)

replication at the cascade level  Cascade Replication (A->B->C)

1) from library: #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 &2Create a new instance from a library 2 instance on the main library server #mysqladmin-uroot--socket=xxx--port=3306-p123456 shutdown#Kill-9(Mysqld_safe process number) #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 fully prepared from library 1) #mysql-uroot--socket=/data/mysql/node2/mysqld.sock-p123456 <D731.sql3) configuration from 1 and slave 2 from 1 authorization: Grant replication Slave on*. * to [email protected]'(from 2IP)'Identified by'Repl'; from 2 configuration replication: Lessd731.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) Joint-level replication Test Main Library: Create database db3; from 1:show databases; (get new library) from 2:show databases; (Get new library)

Monitoring and processing

#查看状态
show slave status;

Success or failure: slave_sql_running:slave_io_running: How long is the delay seconds_behind_master from the library when the problem Last_sql_errnolast_sql_errorlast_io_ Errnolast_io_error replication error Handling common: 1062 (primary key conflict), 1032 (record not present) resolved:

• Manual processing
• Skip replication error: Set global sql_slave_skip_counter=1

Replication error, mostly because of the inconsistency between master and slave data, the best way is to do the data master-slave copy verification; Percona Company has master-slave copy Verification tool;

MySQL master-slave Replication Practice