MySQL Master-slave replication

#################

# # # MySQL master copy # # #

#################

1, MySQL master-slave copy basic working principle:
The master server changes the record to her binary log, copies the binary log of the primary server from the server to its intermediate log, and then changes its data through the intermediate log, thus enabling master-slave replication.
2, Slave two threads:
I/O thread: Writes the binary log of the primary server to the trunk log
SQL Thread: Reads the contents of the trunk log and updates the data.

First, the Environment preparation
Master server: redhat6.5 ip:172.25.24.1 install MySQL Server1
From server: redhat6.5 ip:172.25.24.2 install MySQL Server2

Second, the configuration:

1, the master-slave server for the following actions:
1.1. Consistent version

Tar XF Mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar

Yum install-y mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm

/etc/init.d/mysqld start

1.2. Initialize the table and start MySQL in the background

grep Password/var/log/mysqld.log # #找出mysql初始密码

Mysql-p
1.3, change the root password

mysql> ALTER USER [email protected] identified by ' westos+007 ';
2. Modify Master server Master:
Vim/etc/my.cnf

Add a parameter under [mysqld]
Log-bin=mysql-bin #启动二进制日志系统
Binlog-do-db=test #二进制需要同步的数据库名, if you need to synchronize multiple libraries, such as synchronizing Westos
Library, add another line of "Binlog-do-db=westos", and so on
Server-id=1 #必须为 A positive integer value between 1 and 232–1
Binlog-ignore-db=mysql #禁止同步 MySQL Database

/etc/init.d/mysqld Restart #重启mysql

Mysql> GRANT REPLICATION SLAVE on * * to [e-mail protected] ' 172.25.24.% ' identified by ' westos+007 '; #创建同步帐户, and give permission

mysql> Flush privileges; #刷新

Test:

Server2:

Mysql-u panghu-p-H 172.25.24.1 # #登陆mysql

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
+--------------------+

Server1:

Mysql> Show master status; # #查询 the state of master

+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
|      mysql-bin.000001 | 601 | Test |                   MySQL | |

Note: Do not operate the master server MYSQL again after performing this step to prevent the change of the primary server state value

3. Modify the slave from the server:
Vim/etc/my.cnf

[Mysqld]
Log-bin=mysql-bin ##[not required] enable binary logging
server-id=2 ##[must be] server unique ID, default is 1, generally take the last paragraph of IP, from the server ID number, not the same as the primary ID, if you set up multiple slave servers, each slave server must have a unique Server-id value, must be associated with the primary server and other slave servers Not the same. Server-id values can be considered similar to IP addresses: These ID values uniquely identify each server instance in the replication server cluster.

4. View the Master_log_pos value on the primary server:

cd/var/lib/mysql/

Mysqlbinlog mysql-bin.000001

5. Configure the Slave from the server:
mysql> Change Master to master_host= ' 172.25.24.1 ', master_user= ' Panghu ', master_password= ' westos+007 ', Master_log_    File= ' mysql-bin.000001 ', master_log_pos=514; # #注意不要断开, there are no single quotes around 514 digits.
mysql> start slave; # #启动从服务器复制功能
6. Check the status of the replication function from the server:
Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.25.24.1 # #主服务器地址
Master_user:panghu # #授权帐户名, try to avoid using
master_port:3306 # #数据库端口, some versions do not have this line
Connect_retry:60
master_log_file:mysql-bin.000001
read_master_log_pos:601 # #同步读取二进制日志的位置, greater than or equal to Exec_master_log_pos
relay_log_file:ddte-relay-bin.000002
relay_log_pos:407
relay_master_log_file:mysql-bin.000001
Slave_io_running:yes # #此状态必须 Yes

Slave_sql_running:yes # #此状态必须 Yes

......
Note: The slave_io and slave_sql processes must function normally, that is, the Yes state, otherwise it is an error state (e.g., one of the NO is an error).
The above operation process, the master and slave server configuration is complete.
7. master-Slave server test: Server1

Master server Mysql, build the database, and create a table in this library to insert a piece of data:

mysql> CREATE DATABASE test;
mysql> use test;
Mysql> CREATE TABLE USERTB (
-Username varchar (a) NOT NULL,
Password varchar (+) not NULL);
mysql> desc USERTB;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Username | varchar (15) |     NO | |       NULL | |
| password | varchar (25) |     NO | |       NULL | |
+----------+-------------+------+-----+---------+-------+
mysql> INSERT into USERTB values (' User1 ', ' 111 ');
Mysql> select * from USERTB;
+----------+----------+
| Username | password |
+----------+----------+
| User1 | 111 |
+----------+----------+


Mysql Query from server: Server2

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| SYS |
| Test |
+--------------------+

mysql> use test;
Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| USERTB |
+----------------+

Mysql> select * from USERTB;
+----------+----------+
| Username | password |
+----------+----------+
| User1 | 111 |
+----------+----------+

MySQL Master-slave replication