Mysql master-slave synchronization settings

Premise: the two servers installed with the same version of mysql (in fact, it seems that the version is not the same, but it is safe to use the same version of mysql, after all, I am a little white ). Master Server ip: 192.168.2.19 slave server ip: 192.168.2.155 stop front-end nginx service, both stop php from sending requests to mysql 1. Create

Premise: the two servers installed with the same version of mysql (in fact, it seems that the version is not the same, but it is safe to use the same version of mysql, after all, I am a little white ). Master Server ip: 192.168.2.19 slave server ip: 192.168.2.155 stop front-end nginx service, both stop php from sending requests to mysql 1. Create

Premise: the two servers installed with the same version of mysql (in fact, it seems that the version is not the same, but it is safe to use the same version of mysql, after all, I am a little white ).

Master Server ip: 192.168.2.19 slave server ip: 192.168.2.155

Stop the front-end nginx service and stop requests from php to mysql.

1. Create a user on the master server for Synchronous use on the slave server:

Grant all privileges on *. * to slaveuser @ cluster database IP identified by 'Password ';

2. Stop the mysql service on these two servers:

/Usr/local/mysql/bin/mysqladmin shutdown -- socket =/opt/db/mysql. sock-uroot-ppassword

3. modify the configuration file of the master server

Server-id = 1 # host id, integer
Log_bin =/opt/db/mysql/data/mysql-bin.log # Make sure this file is writable
Read-only = 0 # host, both read and write are allowed
Binlog-do-db = test # You need to back up data and write multiple rows.
Binlog-do-db = anotherdb # Back up data and write multiple rows

4. Pull the table structure and data of the database to be synchronized in the master server from the slave server (enter the root password of the master server)

Scp-r-p root@192.168.2.19:/opt/db/mysql/data/fb_cn/opt/db/mysql/data/

And change the fb_cn user and user group in/opt/db/mysql/data /.

5. Start the mysql Service of the master server (start as needed)

/Usr/local/mysql/bin/mysqld_safe

-- Defaults-file =/opt/db/mysql/my. cnf # mysql configuration file location

-- Basedir =/usr/local/mysql # mysql installation directory

-- Pid-file =/opt/db/mysql. pid # pid file creation location

-- Datadir =/opt/db/mysql/data # data File Location

-- Socket =/opt/db/mysql. sock # location of the sock File

-- Log-error =/opt/db/mysql/err. log # location of the error log File

-- Log-slow-queries =/opt/db/mysql/slowquery. log & # slow query log file location

6. Check the mysql status of the master server and record file and position. It is very important to use it in slave database settings. At this time, ensure that the master server mysql does not have write operations.

/Usr/local/mysql/bin/mysql-uroot -- socket =/opt/db/mysql. sock-p (enter the mysql environment)

> Show master status;

+ ------ + ---- + ----- + ------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------ + ---- + ----- + ------ +

| Mysql-bin.000001 | 106 | myslave | mysql

| + ------ + ---- + ----- + ------ +

7. modify the configuration file of the slave server

Server-id = 2 # host id, integer

Log_bin =/opt/db/mysql/data/mysql-bin.log # binlog

Master-host = 192.168.2.19 # master server IP Address
Master-user = slaveuser # user who uses the master server for master-slave settings

Master-pass = PASSWORD # user password used by the master server for master-slave setting

Master-port = 3306 # master server port number
Master-connect-retry = 60 # The master server is disconnected from the server, and the reconnection time (in seconds)
Replicate-do-db = test # names of the databases for master-slave synchronization, separated by commas

Replicate-do-db = another # Name of the database for master-slave synchronization, separated by commas

8. Start the slave server (based on your own situation)

/Usr/local/mysql/bin/mysqld_safe

-- Defaults-file =/opt/db/mysql/my. cnf # mysql configuration file location

-- Basedir =/usr/local/mysql # mysql installation directory

-- Pid-file =/opt/db/mysql. pid # pid file creation location

-- Datadir =/opt/db/mysql/data # data File Location

-- Socket =/opt/db/mysql. sock # location of the sock File

-- Log-error =/opt/db/mysql/err. log # location of the error log File

-- Log-slow-queries =/opt/db/mysql/slowquery. log & # slow query log file location

9. Enter the mysql environment on the Slave server and configure the Slave information.

/Usr/local/mysql/bin/mysql-uroot -- socket =/opt/db/mysql. sock-p

> CHANGE MASTER

> MASTER_HOST = '192. 168.2.19 ', # master server ip Address

> MASTER_PORT = 3306, # master server mysql service port

> MASTER_USER = 'slaveuser', # username used for master-slave connection in the master server

> MASTER_PASSWORD = 'Password', # user password used for master-slave connection on the master server

> MASTER_LOG_FILE = 'mysql-bin.000001 ', # file read from the master Status of the master server

> MASTER_LOG_POS = 106; # position read from the master Status of the master server

10. Start the Slave Service

> Start slave;

11. View slave server status

> Show slave status \ G;

If you find

Waitingfor master to send event

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

In general, this configuration is still very successful, but due to operational problems, in the configuration process, the brain lacks a string, take replicate-do-db = another directly to the mysql configuration file on the slave server. This is a typical brain attack. It took an hour to complete.