MySQL Settings master-slave synchronization

First of all, we first install the same version of the MySQL database with two servers. I am installing the mariadb-10.0.13 database here.

The first step, environment settings:

My side set is two intranet server, master server ip:10.6.0.187; IP10.6.0.186 from server

System: CentOS 7

Database version: mariadb-10.0.13

Step Two: Configure the MySQL master server (10.6.0.187)

mysql-uroot-p123456 #进入MySQL控制台
Create DATABASE Zabbix default CharSet UTF8;

#建立数据库zabbix
Insert into Mysql.user (Host,user,password) VALUES (' localhost ', ' slave2 ', Password (' 123456 '));

#创建用户slave2

#建立MySQL主从数据库同步用户slave2密码123456

Flush privileges; #刷新系统授权表

#授权用户slave2只能从10.6.0.186 This IP accesses the primary server 10.6.0.187 the database above and has only the permissions of the database backup
Grant Replication Slave on * * to ' slave2 ' @ ' 10.6.0.186 ' identified by ' 123456 ' with GRANT option;

Step three: Export the database from the master server and import it into the database from the server

1. Export Database Zabbix

MySQL dump-u root-p123456 zabbix>/root/zabbix.sql #在MySQL主服务器进行操作, export database Zabbix to/root/zabbix.sql

Note: Before exporting, you can go to the MySQL console and execute the following command

Flush tables with read lock; #数据库只读锁定命令 to prevent data from being written when the database is exported

Unlocktables; #解除锁定

2. Import database to MySQL from server

mysql-uroot-p123456 #进入从服务器MySQL控制台

Create DATABASE Zabbix default CharSet UTF8; #创建数据库

Mysqldump-u root-p123456 zabbix</root/zabbix.sql

Mysql-uslave2-h192.168.21.169-p #测试是否能从从服务器上登录到主服务器

Fourth Step: Modify the configuration file of the primary database

VI/ETC/MY.CNF # Editing a configuration file

server-id=1 #set the server ID, which represents the primary server for 1,Specify a unique server_id for slave. All servers, whether the primary server or the server, are set to server_id. The server_id value can be an integer number (1 ~ 2^31-1), in the same replication group (replicating The server_id of each (/) server in the group must be unique.
Log_bin=mysql-bin # Start the mysq binary log system, note: If the original configuration file already has this line, it is no longer added.
Binlog-do-db=zabbix # The database name that needs to be synchronized, if there are multiple databases, repeat this parameter, one row per database
Binlog-ignore-db=mysql # Out of sync mysql system database
Service mysqld Restart # restart mysql
Mysql-uroot-p # Enter MySQL console
Show master status; to view the primary server, a similar message appears

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6B/E9/wKioL1U6D5awMOaqAAFzU-ZJeKc466.jpg "title=" Image001.png "alt=" Wkiol1u6d5awmoaqaafzu-zjekc466.jpg "/>

Note: The value of file is remembered here: the value of mysql-bin.000035 and position: 1218606, which is used later.

v. Configure MySQL MY.CNF files from the server
VI/ETC/MY.CNF #Editing a configuration file
server-id=2 # configuration file already has a row server-id=1, modified with a value of 2, expressed as from the database
Log-bin=mysql-bin # Start the mysq binary log system, note: If the original configuration file already has this line, it is no longer added.
Replicate-do-db=zabbix #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database
Replicate-ignore-db=mysql # Out of sync mysql system database
: wq! # Save Exit
Service mysqld Restart # restart mysql
Mysql-u Root-p #go to MySQL console
Stop slave; # Stop slave synchronization process
Change Master to master_host= ' 10.6.0.187 ', master_user= ' slave2 ', master_password= ' 123456 ', master_log_file= '     mysql-bin.000035', master_log_pos=1218606; # execute a synchronous statement
Start slave; # turn on the slave sync process
Show SLAVE status\g # View SLAVE sync information, the following appears
650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6B/ED/wKiom1U6DkSy1ol2AAOdtr9P5Hc664.jpg "title=" Image003.png "alt=" wkiom1u6dksy1ol2aaodtr9p5hc664.jpg "/>

Note check:
Slave_io_running:yes
Slave_sql_running:yes
The values for these two parameters are yes, which means the configuration was successful!

Sixth step: Test whether MySQL master and slave can synchronize

Enter the Zabbix library in main MySQL to create a new table

Use Zabbix;

CREATE TABLE test (' id ' int (4), ' name ' char (40));

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6B/E9/wKioL1U6D7WTKzSgAAF0bdn5yyw893.jpg "title=" Image005.png "alt=" Wkiol1u6d7wtkzsgaaf0bdn5yyw893.jpg "/>

This is the table created in the main MySQL;

Check to see if the table is synchronized from MySQL.

Use Zabbix;

Show tables;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6B/ED/wKiom1U6Dmez6FJSAAE5U6T661o694.jpg "title=" Image007.png "alt=" Wkiom1u6dmez6fjsaae5u6t661o694.jpg "/>

Here we see has been synchronized, so far, MySQL master-slave synchronization experiment was done.

Here, thanks to the online great God's tutorial, let me successfully completed the MySQL master-slave configuration. The spirit of the Internet is to share, I will configure my MySQL master from the experience to share to everyone, for all of us to learn together and grow together.

This article is from the "Lemon" blog, be sure to keep this source http://xianglinhu.blog.51cto.com/5787032/1638105

MySQL Settings master-slave synchronization