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

This article mainly tells you about the construction of Linux system mysql master-slave configuration. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on Linux system mysql master-slave configuration building can bring you some practical help.

A master-slave introduction of mysql

Mysql master and slave is also called replication, AB replication. To put it simply, after the two machines An and B are master and slave, they will write data on A, and the other B will also write data, and the two data will be synchronized in real time.

MySQL master and slave is based on binlog, and binlog must be enabled on the master before it can be realized.

The specific process is as follows: 1) the master records the change operation in binlog

2) synchronize the master binlog event (sql statement) to the local machine and record it in relaylog

3) from executing the sql statements in relaylog sequentially

There is a log dump thread on the master to pass the binlog to the slave I / O thread, and there are two threads on the slave, one of which is the Imaco thread used to synchronize the master binlog and generate the relaylog, and the other SQL thread is used to land the sql statements in the relaylog.

Two mysql installation

Let's install another mysql on a machine.

Our mysql was put under / usr/lcoal/mysql before.

1 copy basedir reinitialization

Cd / usr/localcp-r mysql mysql_2cd mysql_2./scripts/mysql_install_db-- user=mysql-- datadir=/data/mysql2

2 redefine the profile

Vim / usr/local/mysql_2/my.cnfbasedir=/usr/local/msyql_2datadir=/data/mysql2port=3307socket=/tmp/mysql2.sock

Note: basedir,datadir and port,socket should be strictly distinguished from the previous mysql.

3 redefine the startup script

Cp / usr/local/mysql/support-files/mysql.server / etc/init.d/mysqld2vim / etc/init.d/mysqld2basedir=/usr/local/mysql_2datadir=/data/mysql2 find the following $bindir/mysqld_safe and insert-- defaults-file=$basedir/my.cnf$bindir/mysqld_safe-- defaults-file=$basedir/my.cnf-- datadir= "$datadir"-- pid-file= "$mysqld_pid_file_path" $other_args > / dev/null & wait_for_pid created "$!"$mysqld_pid_file_path" Return_value=$? Three mysql main library configuration

We use the mysql of port 3306 as the main library, and the main operations are as follows:

1. Edit the configuration file and add server-id to open binlog.

Vim / etc/my.cnf adds the following two sentences: server-id=1 (we set master to 1, slave to 2) log_bin=lv

2 prepare a test library

After restarting the configuration file, restart the mysqld service / etc/init.d/mysqld restart and first give the root user a password mysqladmin-uroot-S / tmp/mysql.sock password 'lvlinux'mysqldump-uroot-S / tmp/mysql.sock-plvlinux mysql > / tmp/mysql.sqlmysql-uroot-S / tmp/mysql.sock-plvlinux-e "create database lv" mysql-uroot-S / tmp/mysql.sock-plvlinux lv < / tmp/mysql.sql

3 create a user to use as synchronization data

Mysql-uroot-S / tmp/mysql.sock-plvlinuxgrant replication slave on *. * to 'repl'@127.0.0.1 identified by' lvlinux';flush tables with read lock;show master status; remarks: the ip followed by repl is the source ip of the slave library, so write native ip because it is on the same machine

Four mysql slave library configuration

We use mysql with port 3307 as the slave library

From the top, do the following

1. Edit the configuration file to define the server-id

Add one line to vim / usr/local/mysql_2/my.cnf, server-id=2

2 configure master-slave relationship from above

After modifying the configuration file, restart the mysqld service.

Give the slave mysql root user a password first

Mysqladmin-uroot-S / tmp/mysql2.sock password 'lvlinux'

Move the lv library on the master to the slave library

Mysql-uroot-S / tmp/mysql2.sock-plvlinux-e "create database lv" mysql-uroot-S / tmp/mysql2.sock-plvlinux lv < / tmp/mysql.sql

Log in to the slave library and do the following

Mysql-uroot-S / tmp/mysql2.sock-plvlinuxstop slave;change master to master_host='127.0.0.1', master_port=3306,master_user='repl', master_password='lvlinux', master_log_file='lvlinux.000001', master_log_pos=429;start slave; will also execute unlock tables on the host

Remarks: master_log_file and master_log_pos fill in the information obtained by the main show master status in turn.

3 View the status of master and slave

Operate from above

Show slave status\ G

If two threads are running properly, the master-slave configuration is successful. If something goes wrong, pay attention.

Seconds_Behind_Master: 0 / / time delay between master and slave

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

5. Several configuration parameters

On the main CVM

Binlog-do-db= / / synchronize only specified libraries

Binlog-ignore-db= / / ignore the specified library

From the CVM

Replicate_do_db=

Replicate_ignore_db=

Replicate_do_table=

Replicate_ignore_table=

Replicate_wild_do_table=

Replicate_wild_ignore_table=

Finally, when we make the master and slave, we should first back up the slave library to be consistent with the master library. When master-slave is turned on, avoid writing data from the library. If you want to restart the master library, you need to close the master and slave first, that is, execute stop slave on the slave library. After the restart, start slave

Linux system mysql master-slave configuration building will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.