Shulou(Shulou.com)06/01 Report--

This article mainly explains the implementation of master-slave synchronization of MySQL database, the content is clear, interested friends can learn, I believe it will be helpful after reading.

Installation environment description

System environment:

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

Database:

Because 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 master library uses port 3306 slave library to install the MySQL database service using port 3307 database data directory / data

Download the software package

Today we use binary installation package to deploy 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 a data directory and a software installation directory

[root@~] # mkdir / data {3306 data 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 a 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 profile

[root@~] # vi / data/3306/ my.cnf [client] port = 3306socket = / data/3306/ MySQL. Socks [MySQL] no-auto- rehash [mysqld] user = mysqlport = 3306socket = / data/3306/mysql.sockbasedir = / application/mysqldatadir = / data/3306/dataopen_files_limit = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_packet = 8Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100thread _ concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2kthread_stack = 192Ktmp_table_size = 2Mmax_heap_table_size = 2Mlong_query_time = 1pid-file = / data/3306/mysql.pidlog-bin = / data/3306/mysql-bin# key points of master-slave synchronization It is not necessary to open relay-log = / data/3306/relay-binrelay-log-info-file = / data/3306/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_insert_buffer_size = 1skip-name-resolveslave-skip-errors = 1032 from the library. The slave library ID cannot be the same [mysqldump] quickmax_allowed_packet = 2m [mysqld _ safe] log-error=/data/3306/mysql3306.errpid-file=/data/3306/mysqld.pid

Database startup script:

[root@~] # vi / mysql.sock 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 instart) start_mysql;;stop) 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 permissions 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

Change 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 installation process

Configure the main library

1) back up the main library

Mkdir / backup

Log in to the main library to create the same household and authorize

[root@~] # mysql-uroot-p123456-S / data/3306/mysql.sockmysql > 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)

Perform table lock operation

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

Back up the main library

[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

Unlock table statu

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

Note: the above operations can also be done in the main database, but it should be noted that after performing the table locking operation, you need to open another window for data backup, and you cannot exit directly to prevent the backup data from being incomplete due to data writing. It is best to use non-interactive operations.

Configure slave library to realize master-slave synchronization

Extract the backup files 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 查看LOG日志 [root@backup ]#cat mysql.log+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 | 424 | | |+------------------+----------+--------------+------------------+ 登陆从库执行下面的操作 mysql>

CHANGE MASTER TO-> MASTER_HOST='192.168.1.7', # server IP-> MASTER_PORT=3306, # main library port-> MASTER_USER='rep', # synchronized 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

Check the synchronization status after waiting for 60s

[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, the master-slave synchronization is successful.

Test master-slave synchronization

The main library creates a database

[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 | +-+ |

View synchronization from the library

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

Indicates that the master-slave synchronization status is normal, or you can create a table in the new data table of the master database, and then insert new data to test the master-slave synchronization status.

After reading the above content, do you have a further understanding of the implementation of master-slave synchronization in MySQL database? if you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.