Enterprise production MySQL master-slave synchronization configuration

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 PROTECTED] ~]# LL /DATA/3306/MYSQL-*-RW-RW---- 1 mysql mysql  7680 JAN  4 14:37 /DATA/3306/MYSQL-BIN.000001-RW-RW---- 1 mysql mysql   221 JAN  4 14:37 /DATA/3306/MYSQL-BIN.000002-RW-RW---- 1 mysql  MYSQL  221 JAN  4 14:37 /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... ...-RW-RW---- 1 mysql mysql  126 Jan   4 21:18 /DATA/3306/MYSQL-BIN.000029-RW-RW---- 1 mysql mysql  107  JAN  4 21:18 /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