MySQL5.7给运行的主库添加从节点
一、高可用简介
高可用架构方案：负载均衡（LVS，nginx），主备系统（MHA，KA，MMM），多活系统（oracle RAC， NDB Cluster，PXC，MGC）；
二、MySQL主从复制（ MySQL Replication ）
2.1主从复制的前提
（1）两个或以上的数据库实例；
（2）主库需要开启二进制日志；
（3）server_id不同，需要区分主从
（4）主库需要建立专门的复制用户；
（5）告诉主库一些复制信息，（ip、port、user、二进制日志起点）
（6）从库应该开启专门的复制进程。
2.2主从搭建
实验目的：在主节点搭建完成的情况下，增加从节点，并同步数据。
1、安装数据库实例
[root@MySQLMaster ~]# rpm -qa | grep mysql [root@MySQLMaster ~]# rpm -qa | grep mariadb mariadb-libs-5.5.64-1.el7.x86_64 [root@MySQLMaster ~]# yum remove mariadb-libs-5.5.64-1.el7.x86_64 -y yum -y install make bison-devel ncures-devel libaio perl-Data-Dumper net-tools bison bison-devel gcc gcc-c++ cmake ncurses ncurses-developenssl openssl-devel
curses-devel ncurses-devel libaio-devel [root@MySQLSlver ~]# mkdir /data/mysql/data -p [root@MySQLSlver ~]# mkdir /application/ -p [root@MySQLSlver ~]# mkdir /data/binlog -p [root@MySQLSlver ~]# useradd -s /sbin/nologin mysql
[root@MySQLMaster application]# tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz [root@MySQLMaster application]# mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql vi /etc/profile export PATH=/application/mysql/bin:$PATH source /etc/profile [root@MySQLMaster ~]# chown -R mysql:mysql /data/ [root@MySQLMaster ~]# chown -R mysql:mysql /application/
[root@MySQLMaster data]# vi /etc/my.cnf [mysqld] server_id=001 port=3306 basedir=/application/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock user=mysql log_bin=/data/binlog/mysql-bin [mysql] socket=/tmp/mysql.sock 从节点server_id=002
[root@MySQLMaster data]# mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/data/mysql/data
[root@localhost etc]# vi /etc/systemd/system/mysqld.service [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf LimitNOFILE=5000
[root@MySQLMaster etc]# systemctl start mysqld [root@MySQLMaster etc]# mysqladmin -uroot -p password Password@123
主库创建用户：
mysql> grant replication slave on *.* to 'replicate'@'192.168.43.7' identified by 'Password@123'; Query OK, 0 rows affected, 1 warning (0.01 sec)
2.3 主从数据复制
主库创建测试数据：
mysql> create database yuan1; Query OK, 1 row affected (0.01 sec) mysql> use yuan1; Database changed mysql> create table yuant1(id int); Query OK, 0 rows affected (0.04 sec) mysql> flush logs; Query OK, 0 rows affected (0.03 sec) mysql> create database yuan2; Query OK, 1 row affected (0.01 sec) mysql> use yuan2; Database changed mysql> create table yuan2t1(id int); Query OK, 0 rows affected (0.04 sec)
此时查看binlog日志：
[root@MySQLMaster binlog]# ls mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.index
备份主库：
[root@MySQLMaster ~]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers>/tmp/full.sql Enter password:
主库备份完成后从库有产生了一些数据：
mysql> create database yuan3; Query OK, 1 row affected (0.01 sec) mysql> show master; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 620 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
拷贝到从库：
[root@MySQLMaster tmp]# scp /tmp/full.sql root@192.168.43.7:/tmp/full.sql
从库恢复数据：
mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> source /tmp/full.sql
查看从库的数据：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | yuan1 | | yuan2 | +--------------------+ 6 rows in set (0.00 sec)
查看备份文件，找到备份完成数据开始的地方：
[root@MySQLMaster tmp]# vi full.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=458;
从库执行，
change master to master_host='192.168.43.6',master_port=3306,master_user='replicate',master_password='Password@123',master_log_file='mysql-bin.000004',master_log_pos=458;
从库开启复制线程：
mysql> start slave; Query OK, 0 rows affected (0.03 sec)
查看数据，主库备份之后的数据通过到从库
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | yuan1 | | yuan2 | | yuan3 | +--------------------+ 7 rows in set (0.00 sec)
查看主从复制状态：
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.43.6 Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 620 Relay_Log_File: MySQLSlver-relay-bin.000002 Relay_Log_Pos: 482 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes
