Mysql主从热备

MySQL主从冷备安装

1-环境准备

（如果服务器上没有安装MySQL，请查看安装文档MySQL安装）

1.俩台服务器
192.168.1.11 #主服务器
192.168.1.22 #从服务器
2.俩台服务器分别安装mysql5.7

#创建mysql用户
groupadd mysql
useradd -r -g mysql mysql
passwd mysql
#创建mysql目录并赋权：
cd /home/
mkdir -p mysql
chown -R mysql:mysql mysql
#移动安装包
tar -zxvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz 
chown -R mysql:mysql mysql-5.7.22-linux-glibc2.12-x86_64
#移动到/usr/local/目录下
mv /home/mysql/mysql-5.7.22-linux-glibc2.12-x86_64 /usr/local/
cd /usr/local/
mv mysql-5.7.22-linux-glibc2.12-x86_64 mysql
#创建mysql目录下的data目录 
cd /usr/local/mysql
mkdir -p data
chown -R mysql:mysql data
#初始化mysql
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
#记住生成的密码：L+df)ydWs283 fpHoM%<Rf29C  lSszitz>w7?c    6(-CRccJ/JbR   WoPyGgt%W1f#

#创建RSA private key
bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
#修改配置文件，将my.cnf里的记录全部删除，并拷贝如下信息：
vi /etc/my.cnf

#注：my.cnf配置文件内容如下
[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
lower_case_table_names = 1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

#设置启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
#修改
vi /etc/init.d/mysqld
basedir=
datadir=
#修改为
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
#快捷命令的软连接，需要设置一下
ln -s /usr/local/mysql/bin/mysql /usr/bin/
#启动mysql
service mysqld start

#进入msql，提示输入密码，直接回车即可
mysql -uroot -p
#修改密码
use mysql;
update user set authentication_string=password("123456") WHERE user='root';
flush privileges;
#查看root用户的详细信息
select * from mysql.user where user='root' \G
#把password_expired 改成不过期
update user set password_expired='N' where user='root';
flush privileges;
exit;
#注掉配置文件my.cnf里的skip-grant-tables
vi /etc/my.cnf
#skip-grant-tables
#重启mysql
service mysqld restart
#设置权限
grant all privileges on *.* to root @'%'indentified by "123456";
#开机自启
/sbin/chkconfig mysqld on



-----------------------------------------------------------------------
#辅助命令：

#复制安装包到从节点
 scp mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz root@192.168.1.22:/opt
#关闭防火墙
 systemctl status firewalld
 systemctl stop firewalld
 systemctl disable firewalld
#重启mysql
 service mysql restart

2-Mysql开启主从

#设置主从用户

grant all on *.* to 'replicate'@'192.168.1.22' identified by '123456';

#设置主机配置文件
[mysqld]
server-id = 1
log-bin=mysql-bin
#设置备机配置文件
[mysqld]
server-id = 2
log-bin=mysql-bin
slave-skip-errors = all

#启动主从
#主机
show master status\G
#查看File和Position
#从机
stop slave;
change master to master_host='192.168.1.11',master_user='replicate',master_password='123456',
master_log_file='mysql-bin.000001',master_log_pos=154;
start slave; 
show slave status\G; 

insert into tb_mobile (mobile) valuse("1234567890")

--------------------------------------------------------------------
#
	File: mysql-bin.000001
	Position: 154
    Binlog_Do_DB:
    Binlog_Ignore_DB:
    Executed_Gtid_Set:
    1 row in set (0.00 sec)