Database master-Slave replication configuration

Primary server: IP address 192.168.56.128,mysql is installed, no user data.

From server: IP address 192.168.56.129,mysql is already installed.

The primary server's MySQL service has started normally.

Master-Slave Replication configuration

Operations on the primary server

(1) Create user and authorize

1 mysql-uroot-p Password

2 mysql>grant replication Slave on * * to [e-mail protected] identified by ' MySQL ';

3 Mysql>flush privileges;

(2) Querying the state of the primary database

1 mysql> show master status;

Note the value of file and position, which you need to use later when you are working from the server.

Configure the slave server

(1) Modify the configuration file from the server/etc/my.cnf

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

(2) Start MySQL service

1 Mysqld_safe--user=mysql &

(3) MySQL log in and execute the synchronous SQL statement, and finally start slave

1 #mysql-uroot-p

2 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;

3 mysql> start slave;

The slave sync feature is activated.

(5) Master-Slave synchronization check

Execute the following statement

1 mysql>show slave Status\g

To check the synchronization from the server, a normal output should look like the following:

Both the slave_io process and the Slave_sql process must function properly, in the state output as: "Slave", otherwise it is not the correct state (such as a value is yes, the other is no).

If user data already exists on the primary database server, the following processing is required for master-slave replication.

(1) The primary database locks the table operation, does not let the data write the action again.

1 Mysql>flush tables Withread lock;

(2) View the status of the primary database

1 mysql>show Master status;

Note the value of file and position for use from the server.

(3) Copy the data files from the master server to the slave server, preferably with tar archive compression

(4) Canceling the primary database lock

1 Mysql>unlock tables;

The operation from the server is skipped as in the previous steps.

Experiment One

Create libraries and tables on the primary database server, insert records, and then log on to the slave server to see if consistent libraries and tables and records are also established.

(1) Operations on the master server

1 mysql>create database first_tb;

① Create library FIRST_TB on the primary database server;

1 mysql>create Table first_db (Idint (3)), name char (10));

② CREATE TABLE FIRST_TB on the primary database server;

1 Mysql>insert into FIRST_TB values (001, ' myself ');

③ insert a record in the table FIRST_TB of the main data server;

(2) Viewing from the server

1 mysql>show databases;

① database first_db Auto-generated

② table FIRST_TB is also automatically generated, data recording also exists.

Experiment Two

Import the database and view the current database on the main library

Create a database buy and import data

When viewed from the library, the database is fully imported.

Database master-Slave replication configuration