17.1 MySQL master-slave introduction
· MySQL master-slave is also called replication, AB replication. Simply speaking is a and b two machines from the back, write the data on a, the other B will follow the writing data, both data real-time synchronization
· MySQL master-slave is based on Binlog, the Lord must open Binlog to carry out master and slave.
• The master-slave process is roughly 3 steps
1) The change operation is recorded in Binlog.
2) from synchronizing the main binlog event (SQL statement) to the machine and recording it in Relaylog
3) Execute sequentially from the SQL statements inside the Relaylog
• The Lord has a log dump thread that is used to communicate with the I/O thread from Binlog
• There are two threads from the top, where I/O threads are used to synchronize the main binlog and generate Relaylog, and another SQL thread is used to place the SQL statements inside the Relaylog
Backup Library
Read the library to relieve the pressure of the main library
17.2 preparatory work
Install MySQL
Simple steps:
wget compile-free installation package
Tar unpacking the compressed package
mv extracted directory to/usr/local/mysql to ensure/usr/local/mysql empty
·. /scripts/mysql_install_db--user=mysql--datadir=/data/mysql Initialize and define users and DataDir (created in advance)
• Edit/etc/my.cnf, define DATADIR and socket locations
CP support-files/mysql.server/etc/init.d/mysqld Copy startup script to/ETC/INIT.D under name mysqld
• Define Basedir,datadir in mysqld
• Start the program/etc/init.d/mysqld start
chkconfig mysqld on can be added to boot boot
Ensure that the MYSQLD service is up and running in the master-slave machine
Installation process reference: http://blog.51cto.com/11530642/2073264
17.3 Configuring the Master
Modify MY.CNF, add server-id=128 (can be determined by IP) and Log_bin=alex (Logbin prefix)
[Email protected] mysql]#/etc/init.d/mysqld Restart # #重启mysqldShutting down MySQL. success! Starting MySQL. success!
Backup and restore MySQL library to aming library as test data
[[email protected] mysql]# mysqldump -uroot -p123456 mysql > /tmp/ mysql.sql # #备份数据库Warning: using a password On the command line interface can be insecure. [[Email protected] mysql]# mysql -uroot -p123456 -e ' create database a4l ' # #创建新库Warning: using a Password on the command line interface can be insecure. [[email protected] mysql]# mysql -uroot -p123456 a4l < /tmp/ mysql.sql # #导入数据库Warning: using a password on the command line interface can be Insecure. [[Email protected] mysql]# ll Total dosage 177292drwx------&NBSP;2&NBSP;MYSQL&NBSP;MYSQL&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;4096&NBSP;3 Month 31 20:51 A4L-RW-RW---- 1 mysql mysql 663420 3 Month 31 20:51 ALEX.000001-RW-RW---- 1 mysql mysql 14 3 Month &NBSP;31&NBSP;20:34&NBSP;ALEX.INDEX-RW-RW---- 1 mysql mysql 56 3 month &NBSP;&NBSP;12&NBSP;21:59&NBSP;AUTO.CNF-RW-RW---- 1 mysql mysql 79691776 3 month &NBSP;&NBSP;31&NBSP;20:51&NBSP;IBDATA1-RW-RW---- 1 mysql mysql 50331648 3 month &NBSP;&NBSP;31&NBSP;20:51&NBSP;IB_LOGFILE0-RW-RW---- 1 mysql mysql 50331648 3 Month &NBSP;12&NBSP;21:47&NBSP;IB_LOGFILE1-RW-RW---- 1 mysql mysql 111979 3 Month &NBSP;&NBSP;31&NBSP;20:34&NBSP;LOCALHOST.LOCALDOMAIN.ERR-RW-RW---- 1 mysql mysql &nbSp;5 3 Month 31 20:34 localhost.localdomain.piddrwx------ 2 mysql mysql 4096 3 Month 18 22:52 mysqldrwx------ 2 mysql mysql 4096 3 Month 18 22:52 mysql2drwx------ 2 mysql mysql 4096 3 Month 12 21:47 performance_ SCHEMADRWX------ 2 mysql mysql 6 3 Month &NBSP;12&NBSP;21:47&NBSP;TESTDRWX------ 2 mysql mysql 324 3 Month 30 22:48 zrlog
Create a user to use as master-slave synchronization:
[[email protected] mysql]# mysql -uroot -p123456warning: using a Password on the command line interface can be insecure. Welcome to the mysql monitor. commands end with ; or \ g.your mysql connection id is 5server version: 5.6.36-log mysql community server (GPL) copyright (c) 2000, 2017, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or Itsaffiliates. other names may be trademarks of their respectiveowners . type ' help; ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> grant replication slave on *.* to ' repl ' @192.168.65.129 identified by ' 123456 '; query ok, 0 rows affected (0.00 sec) ## Create a user to use to synchronize data mysql> flush tables with read lock; ## Lock table to prevent data changes query ok, 0 rows affected (0.01&NBSP;SEC) mysql> show master status;+-------------+----------+--------------+------------------+-------------------+| file | position | binlog_do_db | binlog_ignore_ db | executed_gtid_set |+-------------+----------+--------------+------------------+----------- --------+| alex.000001 | 663631 | | | |+ -------------+----------+--------------+------------------+-------------------+1 row in set (0.00&NBSP;SEC)
To back up a database that needs to be synchronized:
[[email protected] mysql]# mysqldump-uroot-p123456 a4l >/tmp/a4l.sqlwarning:using a password on the command line in Terface can be insecure. [[email protected] mysql]# mysqldump-uroot-p123456 mysql2 >/tmp/my2.sqlwarning:using a password on the command line interface can be insecure. [Email protected] mysql]# mysqldump-uroot-p123456 zrlog >/tmp/zrlog.sqlwarning:using a password on the command Lin E interface can be insecure.
(There are many user names and passwords in MySQL, there is no way to copy all permissions, so do not need to sync)
17.4 Configuration from
Install MySQL
[Email protected] ~]# VIM/ETC/MY.CNF
(Never need bin_log, only the master needs)
[[email protected] ~]# /etc/init.d/mysqld restart # #重启mysqldShutting Down mysql. success! Starting mysql. success! [[email protected] ~]# ls /data/mysql/auto.cnf ib_logfile0 Localhost.localdomain.err mysql performance_schemaibdata1 ib_ Logfile1 localhost.localdomain.pid mysql2 test[[email protected] ~] # mysql -uroot -p123456warning: using a password on the command line interface can be insecure. Welcome to the mysql monitor. commands end with ; or \ g.your mysql connection id is 2server version: 5.6.36 mysql Community server&nbSP; (GPL) copyright (c) 2000, 2017, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or Itsaffiliates. other names may be trademarks of their respectiveowners . type ' help; ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> create database a4l; # #创建数据库Query OK, 1 row affected (0.00 sec) mysql> create database mysql2; query ok, 1 row affected (0.00 sec) mysql> create database Zrlog; Query ok, 1 row affected (0.00 sec) mysql> quitbye # #导入数据 [[email protected] ~]# mysql -uroot -p123456 a4l < /tmp /a4l.sqlwarning: using a password on the command line interface Can be insecure. [[email protected] ~]# mysql -uroot -p123456 mysql2 < /tmp/ my2.sqlwarning: using a password on the command line interface Can be insecure. [[email protected] ~]# mysql -uroot -p123456 zrlog < /tmp/zrlog.sqlWarning: Using a password on the command line Interface can be insecure.
Transfer data:
[[email protected] ~]# scp 192.168.65.128:/tmp/*.sql /tmp/the authenticity of host ' 192.168.65.128 (192.168.65.128) ' can ' t be established. Ecdsa key fingerprint is sha256:pxl0tjsom3464x52jtxx7l+tokm1hb6aumq6lh4asx0.ecdsa key fingerprint is md5:2e:9a:f9:25:b3:80:43:05:a4:3d:3c:9b:48:6e:a0:0e.are you sure you want to continue connecting (yes/no) yeswarning: permanently added ' 192.168.65.128 ' (ECDSA) to the list of known hosts. [email protected] ' s password:a4l.sql 100% 648kb 23.2mb/s 00:00my2.sql 100% 642kb 27.3mb/s 00:00zrlog.sql 100% 10kb 3.1mb/s 00:00
[[Email protected] ~]# mysql -uroot -p123456warning: using a password on the command line interface can be insecure. Welcome to the mysql monitor. commands end with ; or \ g.your mysql connection id is 3server version: 5.6.36 mysql community server (GPL) copyright (c) 2000, 2017, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or Itsaffiliates. other names may be trademarks of their respectiveowners . type ' help; ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> stop slave; Query ok, 0 rows affected (0.00 sec) mysql> change master to master_host= ' 192.168.65.128 ', Master_user= ' Repl ', master_password= ' 123456 ', master_log_file= ' alex.000001 ', master_log_pos= 663631; query ok, 0 rows affected, 2 warnings (0.01 sec) Mysql> start slave; query ok, 0 rows affected (0.01 sec) mysql> show slave status\g 1. row ***************************slave_io_state: waiting for master to send eventmaster_host: 192.168.65.128master_user: replmaster _port: 3306connect_retry: 60master_log_file: alex.000002read_master_log_pos: 120relay_log_ File: localhost-relay-bin.000003relay_log_pos: 278relay_master_log_file: alex.000002slave_io_ Running: yesslave_sql_running: yesreplicate_do_db:replicate_ignore_db:replicate_do_table:replicate_ Ignore_table:replicate_wild_do_table:replicate_wild_ignore_table:last_errno: 0last_error:skip_counter: 0exec_master_log_pos: 120relay_log_space: 613until_condition: noneuntil_log_file:until_log_pos: 0master_ssl_allowed: nomaster_ssl_ca_file:master_ssl_ca_path:master_ssl_cert:master_ssl_cipher: Master_ssl_key:seconds_behind_master: 0master_ssl_verify_server_cert: nolast_io_errno: 0last_io _error:last_sql_errno: 0last_sql_error:replicate_ignore_server_ids:master_server_id: 128master_uuid: bc3bf10d-34ed-11e8-b6a9-000c297e8b1bmaster_info_file: /data/mysql/master.infosql_delay: 0sql_ remaining_delay: nullslave_sql_running_state: slave has read all relay log; waiting for the slave i/o thread to update itmaster_retry_count: 86400master_bind:last_io_error_timestamp:last_sql_error_timestamp:master_ssl_crl:master_ssl_crlpath: Retrieved_gtid_set:executed_gtid_set:auto_position: 01 row in set (0.00&NBSP;SEC)
Two Yes in status is a success:
Back to the primary server, unlock tables
[[email protected] mysql]# mysql -uroot -p123456warning: using a Password on the command line interface can be insecure. Welcome to the mysql monitor. commands end with ; or \ g.your mysql connection id is 2server version: 5.6.36-log mysql community server (GPL) copyright (c) 2000, 2017, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or Itsaffiliates. other names may be trademarks of their respectiveowners . type ' help; ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> unlock tables; Query ok, 0 rows affected (0.00&NBSP;SEC)
Error Rollup:
1,slave_io_running:no
To view error issues:
Last_io_error:fatal error:the slave I/O thread stops because master and slave have equal MySQL server UUIDs; These uuids must is different for replication to work.
Check whether the/data/mysql/auto.cnf uuid of the master and slave server is the same, if you delete one and restart the server again, the change master operation from server MySQL will succeed.
17.5 testing master-Slave synchronization
Defined in MY.CNF: can be in the Lord, or in the
On the primary server
binlog-do-db=//Synchronize only the specified libraries, multiple can be used, to split
binlog-ignore-db=//Ignore specified library
From the server
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
replicate_wild_do_table=//As alex.%, wildcard% supported
replicate_wild_ignore_table=
(Longest use is the last two articles)
Delete the information from the comment table, and the Lord is gone.
The Lord deleted the comment table, and from the comment table there was no
Lord Mysql-uroot aming
Select COUNT (*) from DB;
TRUNCATE TABLE db;
To Mysql-uroot aming from the top
Select COUNT (*) from DB;
The Lord continues to drop table db;
View the DB table from the top
2018-4-2 15 weeks 1 lessons MySQL master-slave configuration