Detailed explanation of the actual process of master-slave synchronization of MySQL database

Detailed explanation of the actual process of master-slave synchronization of MySQL database

This article describes the actual process of master-slave synchronization of MySQL database. Share with you for your reference, the details are as follows:

Continued from the previous article: Getting Started with MySQL Database: Backing Up the Database

Installation Environment Description

System environment:

[root@~]# cat /etc/redhat-release 
CentOS release 6.5 (Final)
[root@~]# uname -r
2.6.32-431.el6.x86_64

database:

Since it is a simulated environment, the master and slave libraries are on the same server, and the server IP address is 192.168.1.7

  • The main library uses port 3306
  • Use port 3307 from the library
  • Database data directory/data

Install MySQL Database Service

Download the package

Today we use the binary installation package to deploy the MySQL database service. For other installation and deployment methods, please refer to the previous article

[root@~]#wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.51-linux2.6-x86_64.tar.gz

Create data directory and software installation directory

[root@~]#mkdir /data{3306,3307} -p
[root@~]#mkdri /application

Decompression software

[root@~]#tar zxf mysql-5.5.51-linux2.6-x86_64.tar.gz 
[root@~]#mv mysql-5.5.51-linux2.6-x86_64 /application/mysql-5.5.51
[root@~]#ln -s /application/mysql-5.5.51 /application/mysql

Create User

[root@~]#groupadd mysql
[root@~]#useradd -g mysql -M mysql

Initialize the database

[root@~]#/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql

[root@~]#/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql

Create a configuration file

[root@~]#vi /data/3306/my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
open_files_limit = 1024
back_log = 600

max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet = 8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
thread_stack = 192K
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1

pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin
#The key point of master-slave synchronization, the slave does not need to open relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 1 #The master and slave IDs cannot be the same [mysqldump]
quick
max_allowed_packet = 2M

[mysqld_safe]
log-error=/data/3306/mysql3306.err
pid-file=/data/3306/mysqld.pid

Database startup script:

[root@~]#vi /data/3306/mysql
#!/bin/sh
port=3306
user="root"
pwd="123456"
Path="/application/mysql/bin"
sock="/data/${port}/mysql.sock"

start_mysql()
{
 if [ ! -e "$sock" ];then
  printf "Starting MySQL...\n"
  /bin/sh ${Path}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
 else
  printf "MySQL is running...\n"
  exit
 fi
}
stop_mysql()
{
 if [ ! -e "$sock" ];then
  printf "MySQL is stopped...\n"
  exit
 else
  printf "Stoping MySQL...\n"
  ${Path}/mysqladmin -u ${user} -p${pwd} -S /data/${port}/mysql.sock shutdown
 fi
}
restart_mysql()
{
 printf "Restarting MySQL...\n"
 stop_mysql
 sleep 2
 start_mysql
}
case $1 in
start)
 start_mysql
;;
stop)
 stop_mysql
;;
restart)
 restart_mysql
;;
*)
 printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

Note: The master-slave library configuration file is the same as the startup file. You only need to modify the port and server-id to complete the configuration.

Authorize the directory and increase the executable permission of the startup file

[root@~]#chown -R mysql.mysql /data
[root@~]#find /data -name mysql -exex chmod +x {} \;

Start the database

[root@~]#/data/3306/mysql start
[root@~]#/data/3307/mysql start

Modify the default database password

[root@~]#mysqladmin -uroot password '123456' -S /data/3306/mysql.sock
[root@~]#mysqladmin -uroot password '123456' -S /data/3307/mysql.sock

Test login, you can log in to two databases to complete the entire installation process

Configure the main library

1) Back up the main database

mkdir /backup

Log in to the main database to create a user and authorize

[root@~]#mysql -uroot -p123456 -S /data/3306/mysql.sock

mysql> grant replication slave on *.* to rep@'192.168.1.%' identified by'123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Execute table lock operation

[root@~]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3306/mysql.sock -e "flush table with read lock;"

Back up the master database

[root@~]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show master status;" >/backup/mysql.log

[root@~]#/application/mysql/bin/mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B |gzip >/backup/mysql.sql.gz

Release the locked table status

[root@~]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3306/mysql.sock -e "unlock tables;"

Note: The above operations can also be performed by logging into the main database. However, it should be noted that after executing the lock table operation, you need to open another window to back up the data. You cannot exit directly to prevent data from being written and causing incomplete backup data. It is best to use non-interactive operations.

Configure the slave library to achieve master-slave synchronization

Unzip the backup file of the main library and restore the database

[root@backup ]#gzip -d mysql.sql.gz

[root@backup ]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3307/mysql.sock < mysql.sql

View LOG

[root@backup ]#cat mysql.log
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 424 | | |
+------------------+----------+--------------+------------------+

Log in to the slave library and perform the following operations

mysql> CHANGE MASTER TO
 -> MASTER_HOST='192.168.1.7', #Server IP
 -> MASTER_PORT=3306, #Master database port-> MASTER_USER='rep', #Synchronized user-> MASTER_PASSWORD='123456', #Synchronized user password-> MASTER_LOG_FILE='mysql-bin.000002', #binlog file-> MASTER_LOG_POS=424; #Position pointmysql> start slave; #Start synchronization

Wait 60 seconds to check the synchronization status

[root@backup ]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|egrep "Seconds_Behind_Master|_Running"
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
   Seconds_Behind_Master: 0

As long as the above situation occurs, it means that the master-slave synchronization is successful

Test master-slave synchronization

Create a database in the main library

[root@backup ~]# mysql -S /data/3306/mysql.sock -e "create database tongbuku"

[root@backup ~]# mysql -S /data/3306/mysql.sock -e "show databases"
+-----------------------------+
| Database |
+-----------------------------+
| information_schema |
|mysql |
| performance_schema |
| test |
|tongbuku |
+-----------------------------+

Check the synchronization status of the slave library

[root@backup ~]# mysql -S /data/3307/mysql.sock -e "show databases"
+-----------------------------+
| Database |
+-----------------------------+
| information_schema |
|mysql |
| performance_schema |
| test |
|tongbuku |
+-----------------------------+

This indicates that the master-slave synchronization status is normal. You can also create a table in the new data table of the master database and insert new data to test the master-slave synchronization status.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:
  • Two ways to solve the problem of MySQL master-slave database not being synchronized
  • Master-slave synchronization configuration of Mysql database
  • This article will show you the principle of MySQL master-slave synchronization
  • How to run MySQL in Docker environment and enable Binlog to configure master-slave synchronization
  • MySQL master-slave synchronization, implementation principle of transaction rollback
  • Detailed explanation of server-id example in MySQL master-slave synchronization
  • Master-slave synchronization configuration and read-write separation of MySQL database
  • MySQL master-slave synchronization principle and application

<<:  The vue project realizes drawing a watermark in a certain area

>>:  Learn the operating mechanism of jsBridge in one article

Recommend

MySQL 5.7.18 release installation guide (including bin file version)

The installation process is basically the same as...

Detailed explanation of the use of vue-resource interceptors

Preface Interceptor In some modern front-end fram...

Vue+el-table realizes merging cells

This article example shares the specific code of ...

Tutorial on building svn server with docker

SVN is the abbreviation of subversion, an open so...

Detailed explanation of ActiveMQ deployment method in Linux environment

This article describes the deployment method of A...

Examples of correct use of interface and type methods in TypeScript

Table of contents Preface interface type Appendix...

Introduction to document.activeELement focus element in JavaScript

Table of contents 1. The default focus is on the ...

jQuery uses hide() and toggle() functions to realize the camera brand display hiding function

Recently, when I was learning jQuery, I came acro...

Disable autocomplete in html so it doesn't show history

The input box always displays the input history wh...

React implements a highly adaptive virtual list

Table of contents Before transformation: After tr...

Detailed explanation of JavaScript object conversion to primitive value

Table of contents Object.prototype.valueOf() Obje...

Use of MySQL query rewrite plugin

Query Rewrite Plugin As of MySQL 5.7.6, MySQL Ser...

How to install Android x86 in vmware virtual machine

Sometimes you just want to test an app but don’t ...

Mysql NULL caused the pit

Using NULL in comparison operators mysql> sele...

How to configure Hexo and GitHub to bind a custom domain name under Windows 10

Hexo binds a custom domain name to GitHub under W...