linux运维、架构之路-MySQL主从复制

一、MySQL主从复制原理图

MySQL主从复制原理：实现主从复制原理是三个线程完成的，主的I/O线程，备的I/O线程与SQL线程

1、首先主库db01需要开启binlog、授权一个replication client账号，锁表进行一次数据全备

2、从库db02、db03导入主库全备数据从而实现主从数据一致性，从库执行change master to将主机、端口、用户名、密码、binlog文件名、binlog位置点存入master.info中

3、从库start slave，首先由从库的I/O线程携带master.info的信息去询问主库，验证rep账号，根据binlog文件和位置点，将新增的binlog文件发回去，同时携带着发送到的文件和位置点，从库收到存到master.info中，收到的binlog日志存放在reply-bin文件中，由从库的SQL执行，先解析binlog文件再先入数据库，执行到的信息存入repay.info中。从库会一直询问主库是否有新的数据，主库有新的数据会返回给从库。

二、MySQL主从复制部署

1、部署环境

[root@db01 tools]# cat /etc/redhat-release

CentOS release 6.9 (Final)

[root@db01 tools]# uname -r

2.6.-.el6.x86_64

[root@db01 tools]# ll /application/

total

lrwxrwxrwx   root  root     -- : mysql -> /application/mysql-5.6./

drwxr-xr-x  mysql mysql  -- : mysql-5.6.

2、服务器规划

服务器名称	外网IP	内网IP	主机名
MySQL数据库db01	172.19.5.51/24	172.16.1.51/24	db01
MySQL数据库db02	172.19.5.52/24	172.16.1.52/24	db02
MySQL数据库db03	172.19.5.53/24	172.16.1.53/24	db03

3、安装MySQL

useradd -s /sbin/nologin -M mysql

cd /server/tools

tar xf mysql-5.6.-linux-glibc2.-x86_64.tar.gz

mkdir -p /application/

mv /server/tools/mysql-5.6.-*-x86_64 /application/mysql-5.6.

ln -s /application/mysql-5.6./ /application/mysql

chown -R mysql.mysql /application/mysql/

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data --user=mysql

\cp /application/mysql/support-files/mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

sed -i 's#/usr/local/mysql#/application/mysql#g' /application/mysql/bin/mysqld_safe /etc/init.d/mysqld

\cp /application/mysql/support-files/my-default.cnf /etc/my.cnf

/etc/init.d/mysqld start

lsof -i:

echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile

source /etc/profile

chkconfig --add mysqld

chkconfig mysqld on

/application/mysql/bin/mysqladmin -u root password ''

4、修改db01、db02、db03数据库配置文件/etc/my.conf

[root@db01 ~]# egrep "log_bin|server_id" /etc/my.cnf

log_bin = mysql-bin  #主库开启log_bin

server_id = 1        #server_id一定不能相同

[root@db02 ~]# grep server_id /etc/my.cnf

server_id = 

[root@db03 ~]# grep server_id /etc/my.cnf

server_id =

5、主库db01操作

mysql -uroot -p123456#登录数据库

grant replication slave on *.* to 'rep'@'172.16.1.%' identified by '';#授权从库用户

flush table with read lock;#锁表

show master status; #查看主库binlog和位置点
+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin. |       |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

 row in set (0.00 sec)

mysqldump -uroot -p123456 -A -B --master-data=|gzip >/tmp/mysql_$(date +%F).sql.gz #全备主库数据

[root@db01 ~]# scp -rp /tmp/mysql_2017--.sql.gz 172.16.1.52:/tmp/ #分发主库全备数据到从库

[root@db01 ~]# scp -rp /tmp/mysql_2017--.sql.gz 172.16.1.53:/tmp/

mysql> unlock table;#解锁表

6、从库db02、db03操作一样

[root@db02 ~]# gzip -d /tmp/mysql_2017--.sql.gz

[root@db02 ~]# mysql -uroot -p123456</tmp/mysql_2017--.sql#从库导入主库全备数据，保证主从数据一致性

Warning: Using a password on the command line interface can be insecure.

mysql -uroot -p'' << EOF

CHANGE MASTER TO

MASTER_HOST='172.16.1.51', #主库IP地址

MASTER_PORT=,

MASTER_USER='rep', #授权用户

MASTER_PASSWORD='',

MASTER_LOG_FILE='mysql-bin.000001',#binlog日志

MASTER_LOG_POS=; #位置点

EOF

mysql -uroot -p123456

start slave;#开启主从同步功能

mysql> show slave status\G#查看主从同步状态，下图看到Slave_IO和Slave_SQL为两个Yes刚证明已经同步

*************************** . row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.16.1.51

                  Master_User: rep

                  Master_Port:

                Connect_Retry:

              Master_Log_File: mysql-bin.

          Read_Master_Log_Pos:

               Relay_Log_File: db02-relay-bin.

                Relay_Log_Pos:

        Relay_Master_Log_File: mysql-bin.

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

巴特西

linux运维、架构之路-MySQL主从复制

最新文章

热门文章