Mysql master-slave Replication

Purpose:

1. The robustness of the Master/Slave server settings is improved. If the master server fails, the slave server that was originally used as a backup can be upgraded to the new master server.

2. You can process user requests separately on the master and slave servers to get a shorter response time.

3. Back up data on the slave server without occupying the system resources of the master server.

 

Scenario Description

Master server: the IP address 192.168.56.128. mysql has been installed and no user data is available.

Slave server: IP address 192.168.56.129, mysql has been installed.

The mysql Service of the master server has been started properly.

 

Master-slave replication Configuration

1. Operations on the master server

(1) Create and authorize a user

 

insert into mysql.user(host,user,password) values( "localhost" , "rep1" ,password( "mysql" ));
Flush privileges;
grant replication slave on *.* to rep1@ 192.168 . 56.129  identified by  'mysql' ;

(2) query the status of the primary database

Mysql> show master status;

 

 

Write down the value of File and Position, which must be used for subsequent operations on the slave server.

1. Configure slave server

(1) modify the configuration file/etc/my. cnf of the slave server

Make "server-id = 2" and make sure this id is not used by other mysql services.

(2) Start the mysql Service

 

Mysqld_safe -- user = mysql &

(3) mysql Logon

Mysql-uroot-p

 

(4) execute the SQL statement for synchronization

mysql> change master to master_host='192.168.56.128',master_user='rep1'

,master_password='mysql',master_log_file='mysql-bin.000004',master_log_pos=477;

 

Correct execution before execution
Mysql> start slave;

 

The slave synchronization function is enabled.

(5) master-slave synchronization check

Execute the following statement:

 

Mysql> show slave status \ G

To check the synchronization of the slave server, a normal output result should be in the following form:

 

Both the Slave_IO process and the slave_ SQL process must run normally, and the status output shows "slave"; otherwise, the status is incorrect (for example, if the value is Yes, the other is No ).

 

If the master database server already has user data, perform the following operations when performing master-slave replication.

(1) The master database locks the table and does not allow data to be written again.

 

mysql>flush tables with read lock;
(2) view the status of the primary database

 

Mysql> show master status;

Write down the values of File and Position for slave servers.

(3) copy the data file of the master server to the slave server. It is best to archive and compress it with tar for processing.

(4) cancel the master database lock

 

Mysql> unlock tables;

The operations on the slave server are the same as those in the previous steps.

1. Lab 1

Create a database and a table on the master database server, insert a record, and log on to the slave server to check whether consistent databases, tables, and records are created.

(1) operations on the master server
Mysql> create database first_tb;

① Create a database first_tb on the master database server;
mysql>create table first_db(id  int ( 3 )),name char ( 10 )); 

② Create the table first_tb on the master database server;
mysql>insert into first_tb values (001,'myself');
 

 

③ Insert records into the table first_tb of the primary data server;

(2) view on the server

1 mysql>show databases;

① Automatic database first_db generation

② The table first_tb is also automatically generated, and data records are also recorded.

Lab 2

Import the database and view the current database on the master database

Create a database buy and import data

Check from the database that the database has been fully imported.

This article is from the "Old Xu's Private food" blog and will not be reposted!
From: http://laoxu.blog.51cto.com/4120547/1198728