MySQL master-slave replication case

Prerequisites:

MySQL in the master and slave operation of the installation and basic configuration of MySQL please refer to my other post: Linux under the installation of MySQL and multi-instance operation " http://ylcodes01.blog.51cto.com/5607366/1841686 "

First, the main library operation

1, 1 view the main library system and MySQL information

[Email protected] ~]# cat/etc/redhat-release CentOS release 6.7 (Final) [[email protected] ~]# uname-r2.6.32-573.el6.x86 _64[[email protected] ~]# mysql--versionmysql Ver 14.14 distrib 5.5.32, for Linux (x86_64) using ReadLine 5.1

1, 2 main Library Settings Server-id and open Binlog

[[email protected] backup]# pwd/server/backup[[email protected] backup]# egrep "Log-bin|server-id"/etc/my.cnf

Server-id =#以IP结尾 (only applicable to single instance), the other Server-id from the library do not repeat with the main library, of course, all from the library also do not repeat, modify must remember to restart the library

log-bin=mysql-bin# Main Library Open Binlog

1, 3 log in to the main library

[Email protected] ~]# mysql-uroot-poldboy123

1, 4 set synchronization user

mysql> grant replication slave on *.* to  ' rep ' @ ' 10.0.0.% '   identified by  ' oldboy123 '; query ok, 0 rows affected  (0.00 sec) mysql> flush privileges; query ok, 0 rows affected  (0.00 sec) mysql> select user,host  from mysql.user;+------+-----------+| user | host      |+--- ---+-----------+| rep  | 10.0.0.%  | |  root | 127.0.0.1 | |  root | ::1       | |       | db01      | |  root | db01      | |       | localhost | |  root | localhost |+------+-----------+7 rows in set  (0.00 sec) mysql> show grants for [Email protected] ' 10.0.0.% '; +----------------------------------------------------------------------------- ------------------------------------------+| grants for [email protected]%                                                                                                     |+------------------------------------------------------------------------------------------ -----------------------------+| grant replication slave on *.* to  ' rep ' @ ' 10.0.0.% '  identified&nbsp by password  ' *fe28814b4a8b3309dac6ed7d3237aded6da1e515 '  |+------------------------------------- ----------------------------------------------------------------------------------+1 row in set   (0.00 SEC)

1, 5 Main Library lock table (transaction cannot be performed at this time)

Mysql> Flush table with read lock; Query OK, 0 rows Affected (0.00 sec)

1, 6 view the main library binlog information

mysql> show master status;+------------------+----------+--------------+------------------+|  file             | position |  binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+---------- --------+| mysql-bin.000002 |      289 |               |                   |+------------------+----------+--------------+--- ---------------+1 row in set  (0.00 sec) mysql> show databases;+---------- ----------+| database           |+---------------- ----+| information_schema | |  mysql              | |  performance_schema | |  test               | |  testdb01           | |  testdb02           | |  testdb03           |+--------------------+7  rows in set  (0.00 SEC)

1, 7 another window to export the main library existing data

[Email protected] backup]# mysqldump-uroot-poldboy123--events-a-b|gzip>/server/backup/mysql_ bak.2016-09-16.sql.gz [[email protected] backup]# lltotal 144-rw-r--r--1 root root 144465 Sep 17:38 mysql_bak.2016-09- 16.sql.gz

1, 8 sent to from the library

[Email protected] backup]# SCP mysql_bak.2016-09-16.sql.gz [email protected]:/server/backup/

1, 9 main Library close lock table status

mysql> unlock tables; Query OK, 0 rows Affected (0.00 sec)

Second, the operation from the library

2, 1 view from the library system and MySQL information

[Email protected] ~]# cat/etc/redhat-release CentOS release 6.7 (Final) [[email protected] ~]# uname-r2.6.32-573.el6.x86 _64[[email protected] backup]# mysql--versionmysql Ver 14.14 distrib 5.5.32, for Linux (x86_64) using ReadLine 5.1[[emai L protected] backup]# Pwd/server/backup

2, 2 set from library Server-id

[Email protected] backup]# egrep "Log-bin|server-id"/etc/my.cnf

Server-id = #以IP结尾 (only for single instance), from the Server-id of the library must not be repeated with the main library and other libraries, modify the database must be restarted

#log-bin=mysql-bin #从库一定不要开启binlog

2, 3 The database backup file sent from the library corresponding decompression main library

[[email protected] backup]# lltotal 144-rw-r--r--1 root root 144465 Sep 17:39 mysql_bak.2016-09-16.sql.gz[[email prote CTED] backup]# gzip-d mysql_bak.2016-09-16.sql.gz [[email protected] backup]# lltotal 520-rw-r--r--1 root root 529834 Se P-17:39 Mysql_bak.2016-09-16.sql

2, 4 import the main library corresponding database data from the library

[Email protected] backup]# mysql-uroot-poldboy123 < mysql_bak.2016-09-16.sql [[email protected] ~]# Mysql-uroot-po Ldboy123

2, 5 view import main Library data results from library

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Test | | TESTDB01 | | TESTDB02 | | TESTDB03 |+--------------------+7 rows in Set (0.00 sec)

2, 6 sets the Synchronize Main Library data command from the library (master-slave replication key command)

mysql> change MASTER to master_host= ' 10.0.0.51 ', master_port=3306,master_log_file= ' mysql-bin.000002 ', Master_log_ Pos=289,master_user= ' rep ', master_password= ' oldboy123 ';

Query OK, 0 rows affected (0.03 sec)

#注意这里的MASTER_LOG_FILE = ' mysql-bin.000002 ' and master_log_pos=289 are the contents of the main library status information corresponding to the main library in the lock table, corresponding to "1, 6 view the main library binlog information".

2, 7 be sure to perform the above change master before start slave

mysql> start slave; query ok, 0 rows affected  (0.00 sec) mysql> show slave status\g  1. row ***************************                slave_io_state: waiting for master  to send event                   Master_Host: 10.0.0.51                   Master_User: rep                   master_port: 3306                 connect_retry:  60              master_log_file: mysql-bin.000002          read_master_log_pos: 289                relay_log_file:  db02-relay-bin.000002                 relay_log_pos: 253        relay_master_log_file :  mysql-bin.000002             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: 289               Relay_Log_Space: 408               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: 0Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno:  0                last_io_error :     &NBsp;           last_sql_errno: 0                Last_SQL_Error:    replicate_ignore_server_ids:               Master_Server_Id: 511 row in set  (0.00 sec) mysql> show  databases;+--------------------+| database            |+--------------------+| information_schema | |  mysql              | |  performance_schema | |  test               | |  testdb01           | |  testdb02           | |  testdb03           |+--------------------+7 rows in  set  (0.00 SEC)

Three, the main library and from the library test

#测试主库数据是否同步到了从库

3, 1 Main library operations

mysql> CREATE DATABASE testdb04; Query OK, 1 row Affected (0.00 sec) mysql> Show databases like ' testdb04 '; +---------------------+| Database (testdb04) |+---------------------+| Testdb04 |+---------------------+1 row in Set (0.00 sec)

3, 2 view from library

Mysql> show databases like ' testdb04 '; +---------------------+| Database (testdb04) |+---------------------+| Testdb04 |+---------------------+1 row in Set (0.00 sec)

#现主从同步测试成功.

This article is from the "You can choose Extraordinary" blog, please be sure to keep this source http://ylcodes01.blog.51cto.com/5607366/1853239

MySQL master-slave replication case