Mysql Master-slave configuration under CentOS 7

Recently playing MySQL master-slave configuration, record here

First, preface

1. Installation of two virtual machines (CentOS 7). IP is 192.168.47.131 and 192.168.47.133 respectively. Where 192.168.47.133 as the primary database, 192.168.47.131 as a Slave database

2. Install MySQL database online. For specific installation methods, please refer to:

3. Create the TestDB database in the master/slave database. (as a synchronized database)

Ii. Concrete Steps

1. master database (Master) configuration

(1) Create a user ' test ' on Master MySQL, and allow the other slave server to access the master remotely, through which the user can read the binary log for data synchronization.

Mysql>Create UserTest//Create a new user//The test user must have replication slave permissions, except that it is not necessary to add unnecessary permissions to the password MySQL. Explain 192.168.47.%, this configuration indicates the server where the REPL user is located,%is a wildcard character, which represents 192.168.47.0-192.168.47. 255 the server can log on to the primary server with test users. Of course you can also specify a fixed IP. MySQL> GRANT REPLICATIONSLAVE on *.*  to 'Test'@'192.168.47.%'Identified by 'MySQL';

　　　　(2) Modify the master MySQL configuration file my.cnf

[[email protected] local]# VIM/etc/my.cnf//In My.cnf's[mysqld]Add the following content server_id=1 Log-Bin=Mysql-Binbinlog-Do-Db=TestDB #允许复制的数据库名称, one row represents a database Binlog-Ignore-Db=MySQL #不复制的数据库

　　　　(3) Restart the database to view.

[[email protected] local] # service Mysqld Restart // Enter the database show master status;

　　　The results are as follows:

+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 699 | TestDB | MySQL | |
+------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

　　See file and position, record it, need it later. mysql-bin.000001 and 699 , respectively.

2. Configuration from database (slave)

　　　　(1) Modify the MySQL configuration file my.cnf

[[email protected] local]# VIM/etc/my.cnf//In My.cnf's[mysqld]Add the following content server_id=2Log-Bin=Mysql-binReplicate-Do-Db=TestDBReplicate-Ignore-Db=Mysql

　　　　(2) Restart database command: Service mysqld restart. Log in to the database.

Mysql>stop Slave; #关闭slave同步进程mysql>Change Master toMaster_host='192.168.47.133', Master_user='Test', Master_password='MySQL', Master_log_file='mysql-bin.000001', Master_log_pos=699;
mysql> start slave; #启动slave同步
Mysql> Show slave status; #查看状态

Note : 　　　 the slave_io_running:yes and slave_sql_running:yes instructions can be synchronized normally.

3, Test.

(1) TestDB Create the T_user table in the master server, and insert a single piece of data.

 create  table   T_user (id  int  primary  key   Auto_increment, NAME  varchar  (50   int  )   Into  t_user (name,age) values  ( " Span style= "color: #ff0000;" >test  , 21 ) ;

　　　　(2) in the TestDB from the server to see if there is a t_user table, and whether there is the corresponding data.

SELECT *  from T_user;

　　　　The T_user table exists from the database and has the corresponding data. Indicates that MySQL master-slave configuration has been successful.

Mysql Master-slave configuration under CentOS 7