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

From:http://www.cnblogs.com/tae44/p/4682810.html

Experimental system: CentOS 6.6_x86_64

Lab Prerequisites: Prepare the build environment in advance, firewall and SELinux are off

The experiment shows that there are 2 hosts in this experiment, such as the topology of IP assignment

Lab Software: mariadb-10.0.20

Experimental topology:

　　　　

First, 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 Mysqlmkdir-pv/mydata/datauseradd-r mysqlchown-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.CNFCP support-files/ Mysql.server/etc/init.d/mysqldchkconfig--add mysqldchkconfig mysqld on

2. Edit the configuration file:

VIM/ETC/MY.CNF--------------------------------datadir =/mydata/data             // Add this line--------------------------------service mysqld start/usr/local/mysql/bin/mysql        //Connectivity Test

　　　　

Second, master-slave replication

In the experiment I first let 19.43 when the primary server, 19.48 for the slave server.

1. Primary server operation:

(1) Edit the MY.CNF, enable the binary log (if defined to a different path, give its MySQL permissions):

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

(2) Define Server-id:

Server-id       = 1

(3) Create an account with copy rights:

/usr/local/mysql/bin/mysql-----------------------------------------GRANT REPLICATION slave,replication CLIENT on *. * to [e-mail protected] identified by ' 123456 ';      Follow the principle of least privilege flush privileges;

(4) record the last binary log information, change Master will use:

SHOW MASTER LOGS;

　　　　

2. Operation from server:

(1) Start the relay log (if defined to a different path, give it MySQL permissions):

Relay_log=/mydata/data/relay-log

(2) Log on from the server is sufficient, close the binary log and reduce disk IO:

#log-bin=mysql-bin             //Annotate it #binlog_format=mixed

(3) Define Server-id:

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

(4) Configure Change MASTER:

/usr/local/mysql/bin/mysql-----------------------------------------Change MASTER to master_host= ' 192.168.19.43 ', Master_user= ' slave ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000002 ', master_log_pos=326;
Show SLAVE status\g                //View status

1.                  Row *************************** slave_io_state:master_host:192.168.19.43 Master_user:slave master_port:3306 connect_retry:60 Master_log_file:mys ql-bin.000002 read_master_log_pos:326 relay_log_file:relay-log.000001 Relay_log_po              S: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_co      Ndition: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:NULLMaster_SSL_Verify_Server_Cert:No Last_io_err no:0 last_io_error:last_sql_errno:0 Last_sql_error:replicate_ignore_serv er_ids:master_server_id:0 Master_ssl_crl:master_ssl_crlpath:u Sing_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 primary server and view it from the server:

CREATE DATABASE Jason;     master server CREATE Database
SHOW DATABASES;            View from server

　　　　

4. If the primary database is not newly established, but has been used for a period of time, and there is already a lot of data in the case, you need to export the primary server data, then import to the slave server, and then according to the above steps for master-slave replication, here will no longer demonstrate.

Export Database Command reference: mysqldump--all-databases--lock-all-tables--master-data=2 > Name.sql

Third, semi-synchronous replication

1. What is semi-synchronous replication?

Because this feature is turned on, the primary server waits for more than one specified from the server to replicate successfully from the server, and then makes other writes to fully synchronize the data from the server and the primary server, regardless of the other slave servers. This guarantees the security of our data to a certain extent. This feature requires a Google-contributed patch, which exists as a plugin. Location of patches in:/usr/local/mysql/lib/plugin/

2. Primary server patching:

/usr/local/mysql/bin/mysql---------------------------------------------INSTALL PLUGIN rpl_semi_sync_master SONAME ' Semisync_master.so '; SHOW GLOBAL VARIABLES like '%semi% ';        View status

　　　　

3. Modify the configuration of the primary server:

SET GLOBAL rpl_semi_sync_master_enabled=1; SET GLOBAL rpl_semi_sync_master_timeout=1000;

4. Patching from the 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 from the server:

SET GLOBAL rpl_semi_sync_slave_enabled=1;
STOP SLAVE;
START SLAVE;

6. master server View Status:

SHOW GLOBAL STATUS like '%semi% ';

　　　　

Iv. Primary master replication

1. In order not to affect the subsequent experiment, we reset the database first because a lot of operations have just been done:

Service mysqld stoprm-rf/mydata/data/*                           //delete database file/usr/local/mysql/scripts/mysql_install_db--user=mysql-- basedir=/usr/local/mysql/--datadir=/mydata/data/        //Initialize MySQL　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

2. Modify MY.CNF:

19.43 on:

VIM/ETC/MY.CNF-------------------------------------------[Mysqld]server-id=1log-bin=/mydata/data/mysql1-bin               //Turn on binary log relay_log=/mydata/data/relay-log              //start relay Log auto-increment-increment = 2                  // Value for each increment Auto-increment-offset = 1                     //Start value

19.48 on:

VIM/ETC/MY.CNF-------------------------------------------[mysqld]server-id=2log-bin=/mydata/data/ Mysql2-binrelay_log=/mydata/data/relay-logauto-increment-increment = 2auto-increment-offset = 2

3. Create a user with copy permissions:

19.43 on:

Service mysqld start/usr/local/mysql/bin/mysql------------------------------------------GRANT REPLICATION SLAVE, REPLICATION CLIENT On * * to [e-mail protected] identified by ' 123456 '; FLUSH privileges;

19.48 on:

Service mysqld start/usr/local/mysql/bin/mysql------------------------------------------GRANT REPLICATION SLAVE, REPLICATION CLIENT On * * to [e-mail protected] identified by ' 123456 '; FLUSH privileges;

4. Look at the binary location:

19.43 on:

SHOW MASTER LOGS;

　　　　

Use the same command on 19.48:

　　　　

5. Configure Change MASTER:

19.43 on:

Change MASTER to master_host= ' 192.168.19.48 ', master_user= ' mmm ', master_password= ' 123456 ', master_log_file= ' Mysql2-bin.000001 ', master_log_pos=642; START SLAVE;

19.48 on:

Change MASTER to master_host= ' 192.168.19.43 ', master_user= ' mmm ', master_password= ' 123456 ', master_log_file= ' Mysql1-bin.000001 ', master_log_pos=642; START SLAVE;

6. View Status:

SHOW SLAVE Status\g

　　　　

After the copy of the demo is no longer shown here, please try it yourself. At this point, all the test demo finished, thank you!

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