MySQL master-slave replication deployment

Objective

MySQL's master-slave replication is based on the binary logging mechanism, which requires the binary logging feature to be turned on. In the specific configuration process, note that the primary server and the slave server are configured with a unique ID number, and from the server must set the host name of the primary server, log file name, file location and other parameters. The process can be divided into the following steps:

(1) master server: Turn on the binary logging function, set the server ID, and restart the MySQL service;

(2) Set the server ID (unique) on all slave servers;

(3) Create an account on the primary server that can read the master server log files for different slave servers, or use a consolidated account;

First, System planning 1, the primary server

Operating system version: RHEL 6.6 x64

Database version: MySQL 5.6

Host ip:192.168.152.129

Host Name: Master

Database name:

ID Number: 1

2. From the server

Operating system version: RHEL 6.6 x64

Database version: MySQL 5.6 (must ensure the MySQL version is consistent)

Host ip:192.168.152.128

Host Name: Slave

Database name:

ID Number: 2

Note: This article is for individual learning, using virtual machine simulation, which can be used as a reference for implementation.

Second, installation preparation 1, storage

You need to allocate enough free space for the source and target databases according to the actual situation

2. Network

Network port Interoperability for Any-any with source and target servers required

3. NTP

Synchronous source and target host clock synchronization is required, for intranet environment, it is recommended to configure the Intranet clock server

4. Other

Configure static IP, host name, IP and hostname bindings, firewalls (open ports or shut down firewalls)

5. Install MySQL

To install MySQL for both the primary and slave servers, ensure that both versions are consistent, or that the slave version is higher than the master version.

Third, master server configuration 1, prepare test data

Prepares the test data and simulates the object to synchronize.

mysql> CREATE DATABASE hr;

mysql> use HR;

Mysql> CREATE TABLE emp (ID int primary Key,name char (20));

mysql> INSERT INTO EMP (id,name) VALUES (1, ' Tom ');

2. Set MY.CNF parameter (1) Copy configuration file

# CP/USR/LOCAL/MYSQL/MY.CNF/ETC/MY.CNF

If necessary, care should be taken to backup the configuration file, and in the production activities understand the awareness of backup.

(2) Parameter setting

Only a few of the necessary parameters are listed below:

log-bin=mysql-bin             enable binary log server-id=1                        server ID, 1- any integer between Binlog- ignore-db=information_schema           Avoid synchronizing the database Binlog-ignore-db=performance_schemabinlog-ignore-db =Mysqlbinlog-do-db=hr                       binary needs to synchronize the database name

Note: The skip-networking parameter is not available in the configuration file, or the server will not be able to connect to the primary server and synchronize data

(3) Restart the server

# Service Mysqld Restart

3. Add sync private permissions for slave users (1) Create a copy account and empower

Mysql> GRANT FILE  on *.*  to 'SLAVE_CP'@'192.168.1.2'Identified by 'Root'; MySQL> GRANT REPLICATIONSLAVE on *.*  to 'SLAVE_CP'@'192.168.1.2'Identified by 'Root'; MySQL>FLUSHPrivileges;

(2) Add Admin user

Grant  All Privileges  on *. *  to [Email protected] ' % '  by ' Root ';

(3) Refresh permissions

privileges;  

Iv. from the server Settings 1, set the MY.CNF parameter (1) Backup configuration file

CP /USR/LOCAL/MYSQL/MY.CNF/ETC/MY.CNF

(2) Modify parameter information

The necessary parameters are as follows:

log-bin=mysql-binserver-id=2                       #从服务器IDbinlog-ignore-db=information_ Schemabinlog-ignore-db=performance_schemabinlog-ignore-db=mysqlreplicate-do-db =hr                 #同步的数据库名replicate-ignore-db=mysql        #屏蔽不同步的数据库log-slave-updatesslave -skip-errors=allslave-net-timeout=

Note: MySQL after 5.6 does not have master-user parameters, or restart the error

(3) Restart the server

# Service Mysqld Restart

Five, database backup, synchronization 1, the database backup

[Email protected] mysql]#/usr/local/mysql/bin/mysqldump-h localhost-uroot-proot--all-databases--lock-all-tables & gt;/tmp/SCP /tmp/dbdump.sql [email protected]:/tmp/~]# mysql-u root-p </tmp/dbdump.sql

Note: From the server you do not need to create a database to synchronize, and the backup script is created automatically.

2. Configure synchronization from the server to the primary server (1), get the primary server binary log information

[Email protected] mysql]# mysql-uroot-penter password:mysql> Flush tables with read lock;        -- read-only lock MySQL> show Master status;mysql> Unlock tables;                       --Release lock

(2) Setting up sync from server

[[email protected] ~]# MySQL-u root–pmysql>Stop Slaves;mysql>Change Master to     -Master_host='192.168.152.129',     -Master_user='SLAVE_CP',     -Master_password='Root',     -Master_log_file='mysql-bin.000003', -Master_log_pos=195;--here the parameter value reference query to the master log informationMySQL>Start Slave;mysql>Show slave status \g

3. Simultaneous verification

Add data to master to see the synchronization of the slave side.

Master node:

[[email protected] ~]# MySQL-Uroot-Pmysql> Create DatabaseTest;mysql>  UseTest;mysql> Create TableUsers (IDint Primary Key, nameChar( -) ); MySQL> Insert  intoUsers (Id,name)Values(1,'AA'); MySQL>  UseHr;mysql>Show Tables;mysql> Create TableBB (IDint, nameChar( -) ); MySQL> Insert  intoEMP (Id,name)Values(2,'BB');

Slave node:

[[email protected] ~] - - Pmysql > Select *  from Test.users;

Error 1, boot from server failed

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

Solve:

Mysql>Reset Slave;mysql>Change Master toMaster_host='192.168.152.129', Master_user='SLAVE_CP', Master_password='Root', Master_log_file='mysql-bin.000005', Master_log_pos=610; MySQL>Start slave;

Reference: https://zhangge.net/5091.html

Reference: http://blog.csdn.net/xlgen157387/article/details/51331244/

MySQL master-slave replication deployment