Mysql replication function-"master-slave" structure _ MySQL

Mysql replication function-"master-slave" structure bitsCN.com

Mysql replication function-"master-slave" structure

1. configure the master server

1. add content in my. ini

2. create an account with "copy permission"

Create an account

Grant all operation permissions on the database to be copied to the created account

View all users

Select distinct concat ('User: ''', User, ''' @ ''', host, '''; ') AS query FROM mysql. user;

View user permissions

3. restart the mysql service.

II. configure slave server

1. add content in my. ini

Note: in the configuration of library my. ini, MySQL 5.1 or later does not support master usage, such.

Available (master-user = repl; master-password = repl; master-port = 3307 ),

If you use this syntax in mysql or later versions, the mysql service cannot be started.

2. restart the mysql server

3. enable slave database replication

A) disable slave database replication.

Stop slave;

B) set the master database connection string

Change master
Master_host = "10.0.0.10 ",
Master_port = 3306,
Master_user = "slave1 ",
Master_password = "feng"
;

Note: it does not matter if you receive a warning, but the password is in plain text.

You can also specify the binary file name and location of the "master database ".

Eg:

You can run the following command to view the master database information:

C) enable the slave database copy function

Start slave;

D) view slave database status

III. test

1. connect to the master database and insert a row of data

2. execute a query on the slave database

IV. description

1. version problems

Slave Database version> = Master Database version

In this experiment: Master Database

Slave Database

　

2. when the slave database is configured for the first time, there should be no "database name to be copied" in the slave database"

When you start the slave for the first time, the database will be created from the slave Database. if it already exists, the database cannot be created.

At this time, the SQL thread on the slave database cannot start

BitsCN.com