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

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/
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?