MySQL master-slave replication configuration case

MySQL master-slave replication configuration case

A master-slave MySQL replication configuration case where the environment is 111.111.111.110 and the slave database is 111.111.111.111.

1. Create a dedicated user for database replication in the master database

-- Run the command on the master database to create the user repl

Mysql-uroot

Grant replication slave, replication client on *. * to repl @ '192. 111.% 'identified by 'repl ';

Exit;

-- Modify the configuration file/etc/my. cnf

Cp/etc/my. cnf/etc/my. cnf. bak

Vi/etc/my. cnf

Modify the master database parameters as follows:

[Mysqld]

Datadir =/var/lib/mysql

Socket =/var/lib/mysql. sock

User = mysql

Log-bin = mysql-bin

Old_passwords = 1

Server_id = 110

Sync_binlog = 1

Relay_log =/var/lib/mysql-relay-bin

# Enable log_slave_updates

Log_slave_updates = 1

Skip_slave_start

[Mysqld_safe]

Log-error =/var/log/mysqld. log

Pid-file =/var/run/mysqld. pid

2. Shut down the database

-- Execute on the master database

Service mysqld stop

3. Back up the master database

-- Execute on the master database

Tar zcvf/tmp/mysql.tar.gz/var/lib/mysql

4. Transmit the backup of the master database to the slave Database

-- Execute on the master database

Rsync-auzvP -- bwlimit = 10240/tmp/mysql.tar.gz 111.111.111.111:/tmp/mysql.tar.gz

Rsync-auzvP -- bwlimit = 1024/etc/my. cnf 111.111.111.111:/etc/my. cnf

5. Restore the backup of the master database in the slave Database

-- Execute in the slave Database

Mv/var/lib/mysql/var/lib/mysqlbak

Mv/tmp/mysql.tar.gz/var/lib/

Tax xvf/var/lib/mysql.tar.gz

6. modify the configuration file/etc/my. cnf in the slave database.

-- Execute in the slave Database

-- Modify the configuration file/etc/my. cnf

Vi/etc/my. cnf

Modify the parameters as follows:

[Mysqld]

Server_id = 111

7. Start the database in the slave Database

-- Execute in the slave Database

Service mysqld start

8. Start the database on the master database

Service mysqld start

Mysql-uroot-p

Show master status;

9. enable replication in the slave database based on the bin log file name and position obtained in the previous step.

-- Execute in the slave database (change the value mysql-bin.0000099 and 0 in the Command master_log_file = 'mysql-bin.0000099 ', master_log_pos = 5 to the value obtained in the previous step)

Change master to master_host = '192. 111.111.110 ',

Master_user = 'repl ',

Master_password = 'repl ',

Master_log_file = 'mysql-bin.00000xx ',

Master_log_pos = 0;

-- Start the replication process

Start slave;

Show slave status \ G

Show processlist \ G

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

MySQL 5.5 master-slave synchronization troubleshooting

MySQL master-slave replication asynchronous semi-sync instance

This article permanently updates the link address: