Linux Learning Summary (51) MySQL master-slave configuration

A MySQL master-slave introduction

MySQL master is also called replication, AB copy. Simply speaking is a and b two machines from the back, write the data on a, the other B will follow the writing data, both data in real-time synchronization.
MySQL master-slave is based on Binlog, the Lord must open Binlog to achieve

The procedure is: 1) The change operation is recorded in Binlog.
2) Synchronize the main Binlog event (SQL statement) to this machine and record it in Relaylog
3) Execute SQL statements from Relaylog in sequence
The Lord has a log dump thread that is used to pass binlog from the I/O thread, with two threads on it, where I/O threads are used to synchronize the main binlog and generate Relaylog, and the other SQL thread uses the SQL statements in the Relaylog

Two MySQL installations

We're going to put a MySQL on one of the machines.
Before our MySQL was placed under the/usr/lcoal/mysql,
1 copy basedir under reinitialization

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

2 Redefining the configuration file

vim /usr/local/mysql/my.cnfbasedir=/usr/local/msyqldatadir=/data/mysql2port=3307socket=/tmp/mysql2.sock

Note: Basedir,datadir, port,socket to be strictly separated from the previously installed MySQL
3 Redefining the startup script

cp usr/local/mysql/support-files/mysql.serve /etc/init.d/mysqld2vim /etc/init.d/mysqld2basedir=/usr/local/mysqldatadir=/data/mysql2找到下面的$bindir/mysqld_safe，插入--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 MySQL as the main repository for Port 3306, and our Lord operates as follows:
1 edit config file, add Server-id open Binlog function

vim  /etc/my.cnf增加下面两句server-id=1   (我们设置主为1，从为2)log_bin=lv

2 Prepare a test library

修改完配置文件后，重启mysqld服务/etc/init.d/mysqld restart先给root用户一个密码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 Creating a user to use to synchronize 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;备注，repl后面跟的ip为从库的来源ip，因为是同一台机器，所以写本机ip

Four MySQL from library configuration

We will port 3307 MySQL as the slave library
From the top action is as follows
1. Edit the configuration file, define Server-id

vim /usr/local/mysql_2/my.cnf增加一行，server-id=2

2 Configuring the master-slave relationship from above
After modifying the configuration file, restart the Mysqld service.
First give a password from the MySQL root user
mysqladmin -uroot -S /tmp/mysql2.sock password ‘lvlinux‘
To move our Lord's LV library to the 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 from the 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;还要到主上执行 unlock tables

Note: Master_log_file and Master_log_pos in turn fill in the information obtained by Lord Show master status
3 Viewing master-slave status
Operate from top
show slave status\G;

Two threads run properly, the master-slave configuration is marked as successful. If the error concerns
seconds_behind_master:0//main from the time of the delay
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:

Five, several configuration parameters

On the primary server
binlog-do-db=//Synchronize only the specified libraries
binlog-ignore-db=//Ignore specified library
From the server
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
replicate_wild_do_table=
replicate_wild_ignore_table=
Finally, we will start from the library back to the same as the main library. Avoid writing data from the library when the master/slave is turned on. If you want to restart the main library, you need to close the master and slave, that is, to execute the stop slave from the library. After restarting, start slave

Linux Learning Summary (51) MySQL master-slave configuration