2018-4-2 15 weeks 1 lessons MySQL master-slave configuration

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