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

=

One: MySQL master-slave replication planning

Master library: (MySQL master) IP:10.0.0.52 PROT:3306 slave library 1: (MySQL slave) IP:10.0.0.52 PORT:3307 slave library 2: (MySQL slave) IP:10.0.0.52 PORT:3308

=

Two: check the environment

(1) check whether multiple instances of MySQL database are started

Netstat-lntup | grep 330tcp 0 0 0.0.0. 0 grep 330tcp 3306 0.0.0. 0 grep 330tcp * LISTEN 20434/mysqld tcp 0 0 0. 0 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0.

(2) enable the Binlog function of the main MySQL library

Grep log-bin / data/3306/my.cnf

(3) set server-id. ID cannot be the same here, otherwise an IO error will occur at the end.

Grep server-id / data/ {3306, 3307, 3308} / my.cnf/data/3306/my.cnf:server-id = 1/data/3307/my.cnf:server-id = 3/data/3308/my.cnf:server-id = 8

=

Third: because there is no data from the database, or the data is not unified, we need to import data.

Wget ftp://10.0.0.1/backup-mysql-salt.tar.gztar xf backup-mysql-salt.tar.gzzcat / root/bak_2016-07-27.sql.gz | mysql- uroot-poldboy-S / data/3306/mysql.sockmysql-uroot-poldboy-S / data/3306/mysql.sockshow databases; # check whether the database is imported into select user,host from mysql.user

=

Four: the operation above the main library (3306)

(1) rep of the user to whom slave access is required for the main database

Mysql-uroot-poldboy-S / data/3306/mysql.sock # Log in to the MySQL database grant replication slave on of the first instance *. * to 'rep'@'172.16.1.%' identified by' oldboy123';flush privileges;show grants for rep@'172.16.1.%'; # to view the user's permission select user,host from mysql.user

Tip: replication slave is a necessary permission for mysql synchronization. Do not authorize all permission here.

(2) Lock the table, view the binlog file and location points, export the main library to complete, and need to lock the table (- x-master-date=2)

Flush table with read lock; # lock table, window cannot exit, exit invalid show master status; # lock table to check the status of the main database, critical point, future recovery will start from 0004 | mysql-bin.000004 | 600457 |

(3) back up the data of the main MySQL database

Mkdir / server/backup/-pmysqldump-uroot-poldboy123-S / data/3306/mysql.sock-A-B-- events | gzip > / server/backup/rep3307_bak$ (date +% F). Sql.gz # since the previously imported database is full, the previous password is oldboy123ls-lrt / server/backup/

(4) unlock the data table of MySQL database and check the status of msater

Unlock table; # unlock the data table show master status of the MySQL database

Tip: if the mysql-bin.000004 description is correct after unlocking, if you move it, the table is not locked.

Tip: if mysqldump adds-F, he will change the refresh binlog.

Five: operations from library 1 (3307)

Tip: make sure that the server_id of the slave library is different from that of the slave library.

Grep server-id / data/ {3306 < 3307} / my.cnf / data/3306/my.cnf:server-id = 1/data/3307/my.cnf:server-id = 3

(1) Import the backup data of the master database into the slave database

Cd / server/backup/gzip-d rep3307_bak2016-07-29.sql.gzmysql-uroot-poldboy-S / data/3307/mysql.sock show databases; | chris |

Delete the chirs database of the master database to check whether the slave database is deleted

Drop database chris; (master library execution) mysql > show databases; (slave library execution)

=

Six: quick configuration from the operation on library 2 (3308)

Idea: since we already have a MySQL slave library, we just need to copy 3307 of the library to 3308

(1) Export the database from the master database and import the slave database

Mysqldump-uroot-poldboy123-S / data/3306/mysql.sock-B-F-- master-data=1-A-- events | gzip > / server/backup/rep3308_bak$ (date +% F). Sql.gz prompt:-- master-data=1 will record the location at the time of backup CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=107

(2) Import data into slave library 3308

Cd / server/backup/gzip-d rep3308_bak2016-07-29.sql.gzmysql-uroot-poldboy-S / data/3308/mysql.sock

< ./rep3308_bak2016-07-29.sql (3)检查3308数据库是否生成master.info文件 ll /data/3308/data/master.info (4)登录3308从库，配置复制参数 mysql -uroot -poldboy -S /data/3308/mysql.sockCHANGE MASTER TO MASTER_HOST='172.16.1.52',MASTER_PORT=3306,MASTER_USER='rep',MASTER_PASSWORD='oldboy123'; (5)上述步骤成功后，开启从库复制开关。 start slave;show slave status\G #G后面一定不要加封号，会报错的 (6)主从是否成功，最关键的为下面的3项状态参数 mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show slave status\G"|egrep "IO_Running|SQL_Running|_Behind_Master"Slave_IO_Running: Yes #这个是I/O线程状态，状态为Yes表示I/O线程工作正常Slave_SQL_Running: Yes #这个是SQL线程状态，状态为Yes表示I/O线程工作正常Seconds_Behind_Master: 0 #这是个复制过程中，从库比主库的延迟的秒数 ========================================================== 七：当我们使用show slave status\G查看主从复制出现故障时，可以看情况跳过 (1)临时停止同步开关 stop slave; (2)将同步指针向下移动一个，如果多次不同步可以重复操作 set global sql_slave_skip_counter =1; (3)开启同步开关 start slave; (4)查看同步信息是否正常 show slave status\G ========================================================== 八：测试主库与从库是否可以实现同步 mysql -uroot -poldboy123 -S /data/3306/mysql.sockmysql -uroot -poldboy123 -S /data/3307/mysql.sockmysql -uroot -poldboy123 -S /data/3308/mysql.sock (1)在主库上创建一个数据库 create database liyajin;（主库执行） (2)查看从库1，从库2是否有同步过来 show databases; （从库1执行）| liyajin |show databases; （从库2执行）| liyajin | (3)将主库中的liyajin数据库删除， mysql>

Drop database liyajin; (main library execution)

(4) check whether the slave library will also be deleted.

Show databases; (execute from Library 1) show databases; (execute from Library 2)

(5) finally, check whether there are any errors in the synchronization status of the slave database. If there are no errors, the MySQL master-slave replication configuration is successful.

Show slave status\ G

Tip: generally, there is a synchronization error when deleting a database. We need to set it to ignore the error.

Nine: now let's package the configured MySQL database into a RPM package

Dependency package: ncurses-devel libaio-devel cmake

Package the LAMP environment with RPM

(1): set the script to be executed after the packaging is completed

Mkdir / server/scripts/-pvim / servermax script bank MySQL house init.shrunken account account binActionBashuseradd-u 505 mysql- s / sbin/nologin-Mchown-R mysql.mysql / data//data/3306/mysql start/data/3307/mysql start/data/3308/mysql start\ cp / application/mysql/bin/* / usr/local/sbin/cat > > / etc/rc.local

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.