MySQL master-Slave synchronization configuration
Introduction: Test environment
Multiple instances of a single MySQL
Host IP Address 10.0.0.52
Master 3306
Salve 3307
First, the main library to open the Binlog service
1.1 Modifying the configuration file 3306/my.cnf
[[email protected] ~]# egrep "Log-bin|server-id"/data/3306/my.cnf log-bin =/data/3306/mysql-binserver-id = 1
1.2 See if the main library is open Binlog service
Method One:
Mysql-uroot-pylh123-s/data/3306/mysql.sock-e "Show variables like ' log_bin '" +---------------+-------+| variable_name | Value |+---------------+-------+| Log_bin | On | <=====on = open +---------------+-------+
Method Two:
See the 3306 instance directory below there are no log files
[[EMAIL&NBSP;PROTECTED]&NBSP;~]#&NBSP;LL&NBSP;/DATA/3306/MYSQL-*-RW-RW---- 1 mysql mysql 7680&NBSP;JAN&NBSP;&NBSP;4&NBSP;14:37&NBSP;/DATA/3306/MYSQL-BIN.000001-RW-RW---- 1 mysql mysql &NBSP;&NBSP;221&NBSP;JAN&NBSP;&NBSP;4&NBSP;14:37&NBSP;/DATA/3306/MYSQL-BIN.000002-RW-RW---- 1 mysql &NBSP;MYSQL&NBSP;&NBSP;221&NBSP;JAN&NBSP;&NBSP;4&NBSP;14:37&NBSP;/DATA/3306/MYSQL-BIN.000003-RW-RW---- 1 mysql mysql 221 jan 4 14:37 /data/3306/ MYSQL-BIN.000004-RW-RW---- 1 mysql mysql 221 jan 4 14:37 / DATA/3306/MYSQL-BIN.000005...&NBSP;...-RW-RW---- 1 mysql mysql 126 Jan &NBSP;4&NBSP;21:18&NBSP;/DATA/3306/MYSQL-BIN.000029-RW-RW---- 1 mysql mysql 107 &NBSP;JAN&NBSP;&NBSP;4&NBSP;21:18&NBSP;/DATA/3306/MYSQL-BIN.000030-RW-RW---- 1 mysql mysql 140 dec 16 09:46 /Data/3306/mysql-bin.index
Second, close the Binlog service from the library
Modifying a configuration file 3307/my.cnf
[[email protected] ~]# egrep "Log-bin|server-id"/data/3307/my.cnf #log-bin =/data/3307/mysql-binserver-id = 3
# # # #如果做级联要打开binlog服务, there are other configurations.
Third, add an account to allow synchronization from the library
1. Create account rep for copy from library
mysql> grant replication Slave on * * to ' rep ' @ ' 10.0.0.% ' identified by ' ylh123 '; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) #<==== flushed to disk
# Replication Slave must have permissions for MySQL sync, do not authorize all
# * * represents all the libraries for all tables, or you can specify specific libraries and tables for replication. Oldboy.test.
# ' rep ' @ ' 10.0.0.% ' rep for sync account. The 10.0.0.% bit authorizes the host network segment, using a% representation to allow the entire 10.0.0.0 segment to be accessed by the rep user.
2. See what permissions the rep user has
mysql> show grants for ' rep ' @ ' 10.0.0.% '; +---------------------------------------------- --------------------------------------+| grants for [email protected]% |+------------------------------------------------------------------------------------+| grant replication slave on *.* to ' rep ' @ ' 10.0.0.% ' IDENTIFIED BY password ' *fe28814b4a8b3309dac6ed7d3237aded6da1e515 ' |+----------------------------------------------------------------------- -------------+1 row in set (0.00 sec) mysql>
Iv. making backups in the main library
4.1 Add a read lock in the main library
Mysql> Flush table with read lock; Query OK, 0 rows affected (0.30 sec) # #不让别的用户往里面写
4.2 Start backing up the database
4.2.1 First look at the state of the main library
mysql> flush table with read lock; query ok, 0 rows affected (0.30 sec) mysql> show master status; +------------------+----------+--------------+------------------+| file | position | binlog_do_db | binlog_ ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000030 | 107 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)
#前面添加了只读锁, 107 is the backup point, starting from this point to backup
Mysql> Show Master logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 7680 | | mysql-bin.000002 | 221 | | mysql-bin.000003 | 221 | | mysql-bin.000004 | 221 | | mysql-bin.000005 | 221 |. | mysql-bin.000029 | 126 | | mysql-bin.000030 | 107 |+------------------+-----------+30 rows in Set (0.00 sec)
4.2.2 Start Backup
Method One:
[Email protected] ~]# mysqldump-uroot-pylh123-s/data/3306/mysql.sock-a-B--events|gzip >/opt/rep.sql.gz
Method Two:
[Email protected] ~]# mysqldump-uroot-pylh123-s/data/3306/mysql.sock-a-B--events--master-data=2 >/opt/rep.sql
# #查看一下是否是从107点备份
[[email protected] ~]# grep "mysql-bin.000030"/opt/rep.sql--change MASTER to master_log_file= ' mysql-bin.000030 ', M aster_log_pos=107;
4.2.3 See if the database is locked. 2663 backup points have not changed
Mysql> Show Master status;+------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000030 | 107 | | |+------------------+----------+--------------+------------------+1 row in Set (0.00 sec)
4.2.4 Unlocking the Read lock
mysql> unlock tables; Query OK, 0 rows Affected (0.00 sec)
Import the data from the main library into the library
Tip: This environment is a MySQL database multiple instances, so if you want to test the two servers only need to push the REP.SQL data from the database can be
Mysql-uroot-pylh456-s/data/3307/mysql.sock </opt/rep.sql
Log in to 3307 from library
Mysql-uroot-p456-s/data/3307/mysql.sockmysql> Change MASTER to, master_host= ' 10.0.0.52 ', Master_po rt=3306, master_user= ' rep ',--master_password= ' ylh123 ', master_log_file= ' mysql-bin.000030 ',-& Gt master_log_pos=107; Query OK, 0 rows affected (0.04 sec)
# # #上面填写的主库信息都放在3307的master in the. info file
[Email protected] ~]# cat/data/3307/data/master.info18mysql-bin.000005266310.0.0.52repoldboy123330660001800.0000
#当主库放binlog日志的时候从库的master. info file will constantly update the information inside
Master_log_file= ' mysql-bin.000030 ', master_log_pos=107;
Six, open the slave switch from the library
mysql> start slave; Query OK, 0 rows Affected (0.00 sec)
# #查看从库是否开启进程IO, SQL
Mysql> Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:10.0.0.52
Master_user:rep
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000030
read_master_log_pos:107
relay_log_file:relay-bin.000003
relay_log_pos:253
relay_master_log_file:mysql-bin.000030
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
Replicate_ignore_db:mysql
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:107
relay_log_space:403
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:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
Master_server_id:1
1 row in Set (0.01 sec)
Seven, the test results are synchronized
#1. Main Library creation Oldboy Library
Mysql-uroot-pylh123-s/data/3306/mysql.sockcreate database Ylh;
#2. Log in to view results from the Vault
Mysql-uroot-pylh123-s/data/3307/mysql.sockmysql> show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | YLH | | Performance_schema |+--------------------+4 rows in Set (0.00 sec)
Tips: the environment is a single MySQL server multiple instances, the operation is all on a MySQL server is the host DB02
How to configure MySQL Multi-instance deployment scenario, stay tuned for my blog http://yulianhui.blog.51cto.com/
This article is from the "Linux Advanced Ops Road" blog, so be sure to keep this source http://yulianhui.blog.51cto.com/10829691/1731537
Enterprise production MySQL master-slave synchronization configuration