Mysql master-slave and master-slave replication configuration demonstration, mysql master-slave

Mysql master-slave and master-slave replication configuration demonstration, mysql master-slave

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 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 // 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 '2016'; // follow the minimal 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 // annotate it # 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 = '2017. 168.19.43 ', MASTER_USER = 'slave', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql-bin.000002 ', MASTER_LOG_POS = 123456;
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: NULLMaster_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 plugin 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 stoprm-rf/mydata/data/* // Delete the database file/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 logs [mysqld] server-id = 1log-bin =/mydata/data/mysql1-bin // enable binary log relay_log =/mydata/data/relay-log // start relay log auto-increment -increment = 2 // The auto-increment-offset value for each increase = 1 // The start value.

On 19.48:

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 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 '123456';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 '123456';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='123456',MASTER_LOG_FILE='mysql2-bin.000001',MASTER_LOG_POS=642;START SLAVE;

On 19.48:

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 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!