write in front: If this article is fortunate enough to be seen by a friend and found wrong, I hope to criticize. If you do not understand the place, we would like to discuss together.
Fundamentals of MySQL Replication
Basic issues that MySQL replication solves
Keep the data of one MySQL server in sync with the data from other MySQL servers.
How MySQL replication works
The working schematic of MySQL replication is shown below (figure from high-performance MySQL version 3rd)
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/E1/wKioL1QdO8Py89y1AAIWXlt9l-I392.jpg "title=" Figure 1 "alt = "Wkiol1qdo8py89y1aaiwxlt9l-i392.jpg"/>
Basic steps for MySQL master-slave replication:
1. Start the binary files on the main library and record the data changes to the binary log;
2, the repository copies the binary log on the main library to its own relay log;
3. The standby library reads events from its own trunk log and replays them onto the repository data.
Case topology diagram
Master-slave replication
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/E1/wKioL1QdPkGz3FyWAACCmnTpd1Q681.jpg "title=" Figure 2 "alt = "Wkiol1qdpkgz3fywaaccmntpd1q681.jpg"/>
Dual Master Model
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/E0/wKiom1QdUN3C-5oIAACDUQB7CRg365.jpg "title=" Figure 3 "alt = "Wkiom1qdun3c-5oiaacduqb7crg365.jpg"/>
Prerequisite : Installation of mariadb-5.5.36 on master and slave two hosts
Installing mariadb-5.5.36
Create a directory where database-related files are stored
# mkdir-pv/mysql/data# mkdir/mysql/binlogs# mkdir/mysql/relaylogs
Create user and user groups for data runtime
# groupadd-r mysql# useradd-g mysql-s/sbin/nologin-m-d/mysql/data/-R MySQL
Modify permissions for database-related file directories
# Chown-r mysql:mysql/mysql/*
Compiling and installing maridb-5.5.36
# tar xf mariadb-5.5.36-linux-x86_64.tar.gz -c /usr/local/# cd /usr/local/# ln -sv mariadb-5.5.36-linux-x86_64/ mysql# cd mysql/# chown -R root:mysql ./*# mkdir /etc/mysql# cp support-files/my-large.cnf /etc/mysql/ my.cnf# vim /etc/mysql/my.cnf datadir=/mysql/data# cp Support-files/mysql.server /etc/rc.d/init.d/mysqld# chkconfig --add mysqld# chkconfig mysqld on# vim /etc/man.config manpath /usr/local/mysql /man# ln -sv /usr/local/mysql/include/ /usr/include/mysql# echo "/usr/local/ mysql/lib/" > /etc/ld.so.conf.d/mysql.conf# ldconfig# vim /etc/profile.d/mysql.sh export path=/usr/local/mysql/bin: $PATH # source /etc/profile.d/mysql.sh# scripts/mysql_install_Db --user=mysql --datadir=/mysql/data/# /etc/rc.d/init.d/mysqld start
Configuring Master-slave replication
Configuring the master server
To configure the basic process:
1. Enable the binary log
2. Define Server-id
3. Create an account with copy rights
Edit configuration file, start binary log and define Server-id
[[email protected] ~]# Vim/etc/mysql/my.cnflog-bin=/mysql/binlogs/master-binserver-id = 1
Create an account with copy permissions
[[email protected] ~]# mysqlmariadb [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to [email protected] ' 1 0.170.%.% ' identified by ' [email protected] '; MariaDB [(None)]> FLUSH privileges;
Configuring the Slave server
To configure the basic process:
1. Turn off the binary log and start the relay log
2. Define Server-id
3. Connect the master server with an account with copy rights
4. Start the I/O thread and SQL thread
Edit the configuration file, start the trunk log function and define Server-id
[Email protected] ~]# vim/etc/mysql/my.cnf #log-bin=mysql-binrelay-log=/mysql/relaylogs/relay-logserver-id = 11
View status information for the master node
MariaDB [(None)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 495 | | |
+-------------------+----------+--------------+------------------+
1 row in Set (0.07 sec)
Connect to the master server using an account with replication privileges
[[email protected] ~]# MySQL
MariaDB [(None)]> change MASTER to master_host= ' 10.170.2.36 ', master_user= ' repluser ', master_password= ' [email Protected] ', master_log_file= 'master-bin.000001', master_log_pos=495;
Starting the I/O thread and SQL thread
MariaDB [(None)]> START SLAVE;
Test Master-slave replication
To add a database on the master server
# MySQL < Mydb.sql
# MySQL < Hellodb.sql
View the database on the master server
[[email protected] ~]# MySQL
MariaDB [(None)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Hellodb |
| MyDB |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
6 rows in Set (0.05 sec)
Viewing the database on a slave server
[[email protected] ~]# MySQL
MariaDB [(None)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Hellodb |
| MyDB |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
6 rows in Set (0.00 sec)
Configuring a dual-master model
Preparatory work
Restore the two hosts that have already installed the MARIADB database to a state that has just been installed
Specific process:
1, stop the MySQL service;
2. Delete all data in/mysql/data/,/mysql/binlogs/,/mysql/relaylogs/directory;
3. Initialize MySQL and start the MySQL service.
Configuring Master1 and MASTER2 Servers
Basic Configuration requirements:
1, Master1 and Master2 have to create an account with copy rights;
2, Master1 and Master2 have to start the relay log and binary log;
3, in order to ensure that the field with automatic growth function can generate the ID correctly, you need to configure Master1 and Master2 to use even and odd ID numbers respectively;
4, Master1 and Master2 are to configure each other as their own master node.
Configuring the Master1 Server
Editing a configuration file
[Email protected] ~]# VIM/ETC/MYSQL/MY.CNF
Log-bin=/mysql/data/master1-bin
Relay-log=/mysql/relaylogs/relay1-bin
Auto-increment-increment = 2
Auto-increment-offset = 1
Server-id = 1
Restart MySQL Service
#/etc/init.d/mysqld Restart
Create an account with copy permissions
[[email protected] ~]# MySQL
MariaDB [(None)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to [email protected] ' 10.170.%.% ' identified by ' [E Mail protected] ';
View status information for Master2
MariaDB [(None)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+--------------------+----------+--------------+------------------+
| master2-bin.000001 | 425 | | |
+--------------------+----------+--------------+------------------+
Connect to Master2
MariaDB [(None)]> change MASTER to master_host= '10.170.2.49', master_user= ' repluser ', master_password= ' [ Email protected] ', master_log_file= 'master2-bin.000001', master_log_pos=425;
Starting the I/O thread and SQL thread
MariaDB [(None)]> START SLAVE;
Configuring the Master2 Server
Editing a configuration file
[Email protected] ~]# VIM/ETC/MYSQL/MY.CNF
Log-bin=/mysql/data/master2-bin
Relay-log=/mysql/relaylogs/relay2-bin
Auto-increment-increment = 2
Auto-increment-offset = 2
Server-id = 11
Restart MySQL Service
#/etc/init.d/mysqld Restart
Create an account with copy permissions
[[email protected] ~]# MySQL
MariaDB [(None)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to [email protected] ' 10.170.%.% ' identified by ' [E Mail protected] ';
View status information for Master1
MariaDB [(None)]> SHOW MASTER STATUS;
+--------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+--------------------+----------+--------------+------------------+
| master1-bin.000001 | 425 | | |
+--------------------+----------+--------------+------------------+
Connect to Master1
MariaDB [(None)]> change MASTER to master_host= '10.170.2.36', master_user= ' repluser ', master_password= ' [ Email protected] ', master_log_file= 'master1-bin.000001', master_log_pos=425;
Starting the I/O thread and SQL thread
MariaDB [(None)]> START SLAVE;
Test the Dual master model
Adding the Hellodb.sql database on the Master1 server
Mys[[email protected] ~]# MySQL < hellodb.sql
On the Master2 server, view the database
[[email protected] ~]# MySQL
MariaDB [(None)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Hellodb |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
5 rows in Set (0.01 sec)
Adding the Mydb.sql database on the Master2 server
[[email protected] ~]# MySQL < mydb.sql
On the Master1 server, view the database
[[email protected] ~]# MySQL
MariaDB [(None)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Hellodb |
| MyDB |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
6 rows in Set (0.05 sec)
Note: Throughout the configuration process, this article uses the master server as a Master1 server and uses the slave server as a Master2 server.
A simple case for MySQL master-slave replication, dual master Model