Master-Slave synchronization tutorial for configuring MySQL Database

Configure master-Slave synchronization of MySQL database (one master and one slave)

First, the main library to open Binlog, Server-id

[Root@master-mysql ~]# grep-e "Server-id|log-bin"/etc/my.cnf
Log-bin =/usr/local/mysql/data/mysql-bin
Server-id = 1
Mysql> Show variables like '%log_bin% ';
+---------------------------------+---------------------------------------+
| variable_name | Value |
+---------------------------------+---------------------------------------+
| Log_bin | On |
| Log_bin_basename | /usr/local/mysql/data/mysql-bin |
| Log_bin_index | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | Off |
| log_bin_use_v1_row_events | Off |
| Sql_log_bin | On |
+---------------------------------+---------------------------------------+
6 rows in Set (0.01 sec)
Mysql> Show variables like '%server_id% ';
+----------------+-------+
| variable_name | Value |
+----------------+-------+
| server_id | 1 |
| Server_id_bits | 32 |
+----------------+-------+
2 rows in Set (0.00 sec)
Note: The above two information must be under the [MYSQLD] module!!!

Second, to authorize from the library

mysql> grant replication Slave on *.* to byrd@ ' 192.168.199.% ' identified by ' admin ';
mysql> flush Privileges;
Mysql> select User,host from Mysql.user;
+------+---------------+
| user | Host |
+------+---------------+
| Root | 127.0.0.1 |
| Byrd | 192.168.199.% |
| Root | :: 1 |
| Root | Lamp |
| Root | localhost |
+------+---------------+
5 rows in Set (0.00 sec)
Set up point data before locking the table:

mysql> CREATE DATABASE Hitest;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Hitest |
+--------------------+
6 rows in Set (0.00 sec)
mysql> use hitest;
Mysql> CREATE TABLE Test (
-> ID int (4) NOT null primary key auto_increment,
-> name char (NOT NULL)
->);
Query OK, 0 rows affected (1.80 sec)
Mysql> Show tables;
+------------------+
| Tables_in_hitest |
+------------------+
| Test |
+------------------+
mysql> INSERT INTO Test (Id,name) VALUES (1, ' zy ');
Mysql> select * from test;
+----+------+
| ID | name |
+----+------+
| 1 | Zy |
+----+------+
Three, lock the table, backup, unlock

Mysql> Flush table with read lock; #锁表
Mysql> Show variables like '%timeout% '; #锁表时间
+-----------------------------+----------+
| variable_name | Value |
+-----------------------------+----------+
| Interactive_timeout | 28800 |
| Wait_timeout | 28800 |
+-----------------------------+----------+
Rows in Set (0.06 sec)
Mysql> Show master status; #binlog日志位置
+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
|     mysql-bin.000004 |              1305 |                  |                   | |
+------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.03 sec)
[Root@master-mysql ~]#/usr/local/mysql/bin/mysqldump-uroot-p '-b-a |gzip >/tmp/all.sql.gz #新窗口备份
Enter Password:
mysql> unlock table; #解锁
############## #解锁后主库操作如下: ###############
Mysql> Use Hitest
mysql> INSERT INTO Test (Id,name) VALUES (2, ' Binghe ');
Mysql> select * from test;
+----+--------+
| ID | name |
+----+--------+
| 1 | Zy |
| 2 | Binghe |
+----+--------+
mysql> CREATE DATABASE Hxy;
############## #解锁后主库操作完成 ~###############
Note: Backup data needs to reopen a new window, otherwise the lock table will automatically expire;

Main Library import to from library

############### #主库操作 ################
[Root@master-mysql tmp]# LL
-rw-r--r--. 1 root root 162236 June 8 21:30 all.sql.gz
[Root@master-mysql tmp]# gzip-d all.sql.gz
[Root@master-mysql tmp]# LL
-rw-r--r--. 1 root root 590351 June 8 21:30 All.sql
############### #主库完成 ################

# #备注: All.sql from the main library are copied to the library server via SCP, SSH, SFTP, etc.

[Root@slave-mysql ~]# grep log-bin/etc/my.cnf
#log-bin =/usr/local/mysql/data/mysql-bin
[root@slave-mysql ~]# grep server-id/etc/my.cnf
Server-id = 2
[root@slave-mysql ~]#/etc/init.d/mysqld restart
[Root@Slave-Mys QL tmp]#/usr/local/mysql/bin/mysql-uroot-p ' admin ' </tmp/all.sql
warning:using a password on the command line I Nterface can be insecure.
[root@slave-mysql tmp]#/usr/local/mysql/bin/mysql-uroot-p ' admin '
mysql> use hitest;
Mysql> SELECT * From Test;
+----+------+
| id | name |
+----+------+
|  1 | zy   |
+----+------+
1 row in Set (0.00 sec)
Six, configuration information from the library

Mysql> Change MASTER to
-> master_host= ' 192.168.199.177 ',
-> master_port=3306,
-> master_user= ' Byrd ',
-> master_password= ' admin ',
-> master_log_file= ' mysql-bin.000004 ',
-> master_log_pos=1305;
Query OK, 0 rows affected, 2 warnings (1.96 sec)
[Root@slave-mysql ~]# Ll/usr/local/mysql/data/master.info
# #备注: Master.info Records master information!
Start Sync from library

mysql> start slave;
Mysql> Show Slave Status\g
Slave_io_running:yes
Slave_sql_running:yes
seconds_behind_master:0
Viii. Results Test

mysql> use hitest;
Mysql> select * from test;
+----+--------+
| ID | name |
+----+--------+
| 1 | Zy |
| 2 | Binghe |
+----+--------+
2 rows in Set (0.00 sec)
[Root@master-mysql ~]#/usr/local/mysql/bin/mysql-uroot-p '-e ' Create database Zhihu; " #主库建立了一个zhihu的数据库
Enter Password:
[Root@slave-mysql ~]#/usr/local/mysql/bin/mysql-uroot-p ' e ' show databases like ' Zhihu ' ";
Enter Password:
+------------------+
| Database (Zhihu) |
+------------------+
| Zhihu |
+------------------+

Configure the MySQL database master-Slave synchronization (dual-master)

Already configured:

Main Library: 192.168.199.177
From library: 192.168.199.178

[Root@master-mysql ~]# egrep "server-id|log-slave|log-bin|auto_increment|slave-skip-errors"/ETC/MY.CNF
Log-bin =/usr/local/mysql/data/mysql-bin    #必须
Server-id = 1    #必须
log-slave-updates    #必须
auto_increment_increment = 2    #必须
Auto_increment_ offset = 1    #必须
slave-skip-errors = 1032,1062,1007    #非必须, recommended
################ ####### #主库, ######################## from library separator
[root@slave-mysql data]# egrep "server-id|log-slave|log-bin|auto_ Increment|slave-skip-errors|read-only "/etc/my.cnf
#log-bin =/usr/local/mysql/data/mysql-bin
Server-id = 2
Log-slave-updates
Log-bin =/usr/local/mysql/data/mysql-bin
#read-only    #双主, this option to comment out
Slave-skip-errors = 1032,1062,1007
Auto_increment_increment = 2    #ID自增间隔
Auto_increment_ offset = 2    #ID初始位置
192.168.199.178:

mysql> stop Slave;
Mysql> Flush table with read lock;
Mysql> Show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
|      mysql-bin.000004 |              120 |                  |                   | |
+------------------+----------+--------------+------------------+-------------------+
Mysql> system/usr/local/mysql/bin/mysqldump-uroot-p '-a-b >/tmp/192.168.199.178.sql #如果主, consistent and not necessary
mysql> unlock tables; #同上
mysql> system Ls-l/tmp/
-rw-r--r--. 1 root 2887406 June 22:24 192.168.199.178.sql
mysql> start slave;
192.168.199.177:

[Root@master-mysql ~]#/usr/local/mysql/bin/mysql-uroot-p ' </tmp/192.168.199.178.sql #如果主, consistent and not necessary
mysql> Update Mysql.user Set Password=password (' admin ') where user= ' root ';
[Root@master-mysql ~]# cat |/usr/local/mysql/bin/mysql-uroot-p ' admin ' <<eof #必须
> Change MASTER to
> master_host= ' 192.168.199.178 ',
> master_port=3306,
> master_user= ' Byrd ',
> master_password= ' admin ',
> master_log_file= ' mysql-bin.000004 ',
> master_log_pos=120;
> EOF
mysql> start slave;
Mysql> Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.199.178
Master_user:byrd
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000004
read_master_log_pos:938
relay_log_file:mysqld-relay-bin.000002
Relay_log_pos:1101
relay_master_log_file:mysql-bin.000004
Slave_io_running:yes
Slave_sql_running:yes
last_errno:0
skip_counter:0
exec_master_log_pos:938
relay_log_space:1275
Until_condition:none
until_log_pos:0
Master_ssl_allowed:no
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
last_sql_errno:0
Master_server_id:2
master_uuid:34d672c3-d292-11e3-9ff5-00155dc7834c
Master_info_file:/usr/local/mysql/data/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave has read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Test:
192.168.199.177:

Mysql> use hitest;
mysql> CREATE TABLE ' ces ' (
   -> ' rel_id ' bigint () not NULL auto_increment ' ID ',
   -> ' TITLE ' varchar (255) Not NULL COMMENT ' Biaoti ',
   -> PRIMARY KEY (' R el_id ')
   ->) engine=innodb auto_increment=1 DEFAULT Charset=utf8;
mysql> INSERT into CES (TITLE) VALUES (' Test ');
mysql> INSERT into CES (TITLE) VALUES (' Test ');
mysql> INSERT into CES (TITLE) VALUES (' Test ');
mysql> INSERT into CES (TITLE) VALUES (' Test25 ');
mysql> select * from CES;
+--------+-------+
| rel_id | TITLE |
+--------+-------+
|      1 | test  |
|      3 | test  |
|      5 | test  |
|     | test25|
+--------+--------+
3 rows in Set (0.03 sec)
192.168.199.178:

mysql> use hitest;
mysql> INSERT into CES (TITLE) VALUES (' Test26 ');
mysql> INSERT into CES (TITLE) VALUES (' Test28 ');
mysql> INSERT into CES (TITLE) VALUES (' Test30 ');
Mysql> select * from CES;
+--------+--------+
| rel_id | TITLE |
+--------+--------+
| 1 | Test |
| 3 | Test |
| 5 | Test |
| 26 | Test26 |
| 28 | Test28 |
| 30 | Test30 |
+--------+--------+
Rows in Set (0.00 sec)

Description: If a master, a bundle is done, just know from the location point of the library (show Master status), then before the main library execution (change master), before the main library open slave (start slave). Where the database backup steps can be omitted, if the principal and subordinate some database inconsistencies are the same as the operation!