MySQL database master-slave and master-master replication configuration demonstration

MySQL database master-slave and master-master replication configuration demonstration

Experimental System: CentOS 6.6 _ x86_64

Prerequisites: Prepare the compiling environment in advance, and disable both firewall and selinux.

Tutorial Description: There are two hosts in this experiment, and IP addresses are allocated as topology.

Experimental software: mariadb-10.0.20

Tutorial topology:

1. Install mysql

1. Compile and install mariadb:

Tar xf mariadb-10.0.20-linux-x86_64.tar.gz-C/usr/local/
Cd/usr/local/
Ln-sv mariadb-10.0.20-linux-x86_64 mysql
Mkdir-pv/mydata/data
Useradd-r mysql
Chown-R mysql. mysql/mydata/data/
Cd mysql/
Chown-R root. mysql.
Scripts/mysql_install_db -- user = mysql -- datadir =/mydata/data/
Cp support-files/my-large.cnf/etc/my. cnf
Cp support-files/mysql. server/etc/init. d/mysqld
Chkconfig -- add mysqld
Chkconfig mysqld on

2. Edit the configuration file:

Vim/etc/my. cnf
--------------------------------
Datadir =/mydata/data // Add this row
--------------------------------
Service mysqld start
/Usr/local/mysql/bin/mysql // connection test

　

Ii. Master-slave Replication

In this experiment, I first set 19.43 as the master server and 19.48 as the slave server.

1. master server operations:

(1) edit my. cnf and enable binary logs (if other paths are defined, grant mysql permissions ):

Log-bin =/mydata/data/mysql-bin

(2) define server-id:

Server-id = 1

(3) create an account with the copy permission:

/Usr/local/mysql/bin/mysql
-----------------------------------------
Grant replication slave, replication client on *. * TO slave@192.168.19.48 identified by '000000'; // follow the minimum permission Principle
Flush privileges;

(4) record the final binary log information, which will be used when changing the MASTER:

Show master logs;

2. slave server operations:

(1) Start relay logs (if other paths are defined, grant mysql permissions to them ):

Relay_log =/mydata/data/relay-log

(2) It is enough to use relay logs on the slave server to disable binary logs and reduce disk IO:

# Log-bin = mysql-bin // comment it out
# Binlog_format = mixed

(3) define server-id:

Server-id = 2 // cannot be the same as the master server

(4) Configure change master:

/Usr/local/mysql/bin/mysql
-----------------------------------------
Change master to MASTER_HOST = '192. 168.19.43 ', MASTER_USER = 'slave', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql-bin.000002 ', MASTER_LOG_POS = 192;
Show slave status \ G // view the STATUS

* *************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.19.43
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 326
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_ SQL _Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 326
Relay_Log_Space: 248
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)

(5) Start io thread and SQL thread:

Start slave;

3. Create a database on the master server and view it from the server:

Create database jason; // CREATE a DATABASE on the master server
Show databases; // view from the server

4. if the primary database is not newly created but used for a period of time and there is already a lot of data in it, you need to first export the data of the primary server and then import it to the slave server, then perform master-slave replication according to the above steps, which will not be demonstrated here.

For Database Export commands, see mysqldump -- all-databases -- lock-all-tables -- master-data = 2> name. SQL.

Iii. semi-synchronous Replication

1. What is semi-synchronous replication?

After this function is enabled, the master server only waits for the specified slave server in multiple slave servers to replicate successfully before performing other write operations, make the data on the slave server and the master server completely synchronized, regardless of other slave servers. This ensures the security of our data to a certain extent. This function requires a patch contributed by google. It exists as a plug-in. Where the patch is stored:/usr/local/mysql/lib/plugin/

2. patch the master server:

/Usr/local/mysql/bin/mysql
---------------------------------------------
Install plugin rpl_semi_sync_master SONAME 'semisync _ master. so ';
Show global variables like '% semi %'; // view the status

3. modify the configuration of the master server:

Set global rpl_semi_sync_master_enabled = 1;
Set global rpl_semi_sync_master_timeout = 1000;

4. patch the slave server:

/Usr/local/mysql/bin/mysql
---------------------------------------------
Install plugin rpl_semi_sync_slave SONAME 'semisync _ slave. so ';
Show global variables like '% semi % ';

5. modify the configuration on the slave server:

Set global rpl_semi_sync_slave_enabled = 1;
Stop slave;
Start slave;

6. Check the status of the master server:

Show global status like '% semi % ';

4. Master-master Replication

1. Because we have done a lot of Operations just now, in order not to affect the subsequent experiments, we should first reset the database:

Service mysqld stop
Rm-rf/mydata/data/* // delete database files
/Usr/local/mysql/scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysql/-- datadir =/mydata/data // initialize mysql

2. modify my. cnf:

On 19.43:

Vim/etc/my. cnf
-------------------------------------------
[Mysqld]
Server-id = 1
Log-bin =/mydata/data/mysql1-bin // enable binary log
Relay_log =/mydata/data/relay-log // start relay log
Auto-increment = 2 // The value of each increase
Auto-increment-offset = 1 // start value

On 19.48:

Vim/etc/my. cnf
-------------------------------------------
[Mysqld]
Server-id = 2
Log-bin =/mydata/data/mysql2-bin
Relay_log =/mydata/data/relay-log
Auto-increment = 2
Auto-increment-offset = 2

3. Create a user with the copy permission:

On 19.43:

Service mysqld start
/Usr/local/mysql/bin/mysql
------------------------------------------
Grant replication slave, replication client on *. * TO mmm@192.168.19.48 identified by '2013 ';
Flush privileges;

On 19.48:

Service mysqld start
/Usr/local/mysql/bin/mysql
------------------------------------------
Grant replication slave, replication client on *. * TO mmm@192.168.19.43 identified by '2013 ';
Flush privileges;

4. view the binary location:

On 19.43:

Show master logs;

Use the same command on 19.48:

5. Configure change master:

On 19.43:

Change master to MASTER_HOST = '192. 168.19.48 ', MASTER_USER = 'mmm', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql2-bin.000001', MASTER_LOG_POS = 192;
Start slave;

On 19.48:

Change master to MASTER_HOST = '192. 168.19.43 ', MASTER_USER = 'mmm', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql1-bin.000001', MASTER_LOG_POS = 192;
Start slave;

6. view the status:

Show slave status \ G

The subsequent copy demonstration will not be demonstrated here. Please try it on your own. So far, all the test demonstrations have been completed. Thank you!

-------------------------------------- Split line --------------------------------------

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

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: