Master slave synchronization of MySQL database

Next: MySQL database introduction backup database

Installation environment description

System environment:

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

Database:

Because it is a simulation environment, the master-slave database is on the same server, and the server IP address is 192.168.1.7

The main library uses port 3306
The slave library uses port 3307
Database data directory / data

Install MySQL database service

Download package

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

[[email protected]~]#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

[[email protected]~]#mkdir /data｛3306，3307｝ -p
[[email protected]~]#mkdri /application

Decompression software

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

Create user

[[email protected]~]#groupadd mysql
[[email protected]~]#useradd -g mysql -M mysql

Initialize database

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

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

create profile

[[email protected]~]#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 does not need to be opened on the slave database
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 ා master database and slave database ID 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:

[[email protected]~]#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 configuration file of master-slave database is the same as that of startup file. You only need to modify the port and server ID to complete the configuration

Authorization directory and increase the executable rights of the startup file

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

Start the database

[[email protected]~]#/data/3306/mysql start
[[email protected]~]#/data/3307/mysql start

Modify default database password

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

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

Configure master database

1) Backup main database

mkdir /backup

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

[[email protected]~]#mysql -uroot -p123456 -S /data/3306/mysql.sock

mysql> grant replication slave on *.* to [email protected]'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 lock table operation

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

Backup main database

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

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

Unlock table status

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

Note: the above operations can also log in to the main database, but it should be noted that after the lock table operation, another window needs to be opened for data backup, and it is not allowed to exit directly to prevent incomplete backup data caused by data writing. It is best to use non interactive operations.

Configure slave database to realize master-slave synchronization

Decompress the backup file of the main database and restore the database

[[email protected] ]#gzip -d mysql.sql.gz

[[email protected] ]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3307/mysql.sock < mysql.sql

View log log log

[[email protected] ]#cat mysql.log
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      424 |              |                  |
+------------------+----------+--------------+------------------+

Log in from the library and perform the following operations

mysql> CHANGE MASTER TO
    -> MASTER_ Host = 192.168.1.7 ', server IP
    -> MASTER_ Port = 3306, ා main library port
    -> MASTER_ User = rep ', synchronous user
    -> MASTER_ Password = 123456 ', ා synchronized user password
    -> MASTER_ LOG_ File ='mysql-bin.000002 ', binlog file
    -> MASTER_ LOG_ POS = 424; location point
MySQL > start slave; ා enable synchronization

Wait for 60s to check the synchronization status


[[email protected] ]# 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, the master-slave synchronization is successful

Test master slave synchronization

The master database creates a database

[[email protected] ~]# mysql -S /data/3306/mysql.sock -e "create database tongbuku"

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

View synchronization from library

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

It 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 then insert new data to test the master-slave synchronization status

For more related technical articles about mysql, please pay close attention to the technical road of migrant workers. Official account of brother brother WeChat’s official account: the road of technology for brother laborers, and the way of WeChat’s public address. The key words: 1024 You can obtain a copy of the latest technical dry goods, including system operation and maintenance, database, redis, mogodb, e-book, java basic course, Java practical project, comprehensive course for architects, architect practical project, big data, docker container, elk stack, machine learning, bat interview intensive lecture video, etc.
Master slave synchronization of MySQL database