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

Source: Internet
Author: User
Experimental System: CentOS6.6 _ x86_64 prerequisites: Prepare the compiling environment in advance. Firewall and selinux both disable the experiment. Note: There are two hosts in this experiment, and IP Address Allocation is similar to topology.

Experimental System: CentOS 6.6 _ x86_64 prerequisites: Prepare the compiling environment in advance. Both firewall and selinux disable the experiment. Note: There are two hosts in this experiment, and IP Address Allocation is shown as topology.

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/
Ln-sv mariadb-10.0.20-linux-x86_64 mysql
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:

Grant replication slave, replication client on *. * TO slave@ 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:

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 ***************************
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
Last_Errno: 0
Skip_Counter: 0
Exec_Master_Log_Pos: 326
Relay_Log_Space: 248
Until_Condition: None
Until_Log_Pos: 0
Master_SSL_Allowed: No
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_ SQL _Errno: 0
Last_ SQL _Error:
Master_Server_Id: 0
Using_Gtid: No
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?

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.