MySql安装与基本配置 双主备份
环境清理
1. 卸载centos 7自带的mariadb
查看
rpm -qa|grep mariadb
卸载
rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
注: ’mariadb-libs-5.5.56-2.el7.x86_64‘ 为本地版本
2.卸载自带的mysql
查看
rpm -qa | grep mysql
卸载
rpm -e --nodeps mysql57-community-release-el7-8.noarch
安装
1.更改下载源
wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
# 二选一 两个源
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
2.安装源
yum -y localinstall mysql57-community-release-el7-8.noarch.rpm
注:如果报-bash: wget: command not found,则表明没有安装wget,需要安装,安装命令如下
yum -y install wget
3.查看mysql源是否安装成功
yum repolist enabled | grep "mysql.*-community.*"
4.进入相关文件查看
cd /etc/yum.repos.d/
5.安装
yum -y install mysql-community-server
基础命令
1.启动mysql服务
systemctl start mysqld
# 二选一
systemctl start mysqld.service
2.检测启动状态
systemctl status mysqld.service
3.设置开机自启
systemctl enable mysqld
systemctl daemon-reload
4.关闭防火墙
systemctl stop mysqld.service
注:不关闭远程访问不了
5.重启服务
systemctl restart mysqld.service
修改root密码
1.修改root本地登录密码
mysql安装完成之后,在/var/log/mysqld.log文件中给root生成了一个默认密码。
grep 'temporary password' /var/log/mysqld.log
返回结果:2018-08-17T12:49:33.835763Z 1 [Note] A temporary password is generated for root@localhost: <‘临时密码’>
2.登录mysql
使用临时密码登录,用该密码登录到服务端后,必须马上修改密码,不然会报错误
mysql -u root -p
3.修改密码
必须得先登录进mysql才行
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Xy12.top123456';
# 二选一
set password for 'root'@'localhost'=password('Xy12.top123456');
注:mysql5.7默认安装了密码安全检查插件(validate_password),默认密码检查策略要求密码必须包含:大小写字母、数字和特殊符号,并且长度不能少于8位。否则会提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements错误
添加用户
1.登录mysql
mysql -u root -p
2.添加用户
GRANT ALL PRIVILEGES ON *.* TO 'xiaoyang'@'%' IDENTIFIED BY 'root123' WITH GRANT OPTION;
注: xiaoyang是用户名, root123是密码
MySql双主备份
双主备份也就是两台服务器进行相互备份,一台服务器中数据改变另一台也跟着改变。
是主从备份的变体
A机配置
1.修改A机配置文件
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=1 # 设置服务器编号必须唯一
auto_increment_offset=1
auto_increment_increment=2
gtid_mode=on
enforce_gtid_consistency=on
log-bin=mysql-bin
# slow query
slow_query_log=on
slow_query_log_file=/var/lib/mysql/log/slow-query.log
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
2.在A机上创建一个用户,用于B机访问
create user B@'192.168.0.102' identified by '123123';
grant replication slave on *.* to B@'192.168.0.102';
注:创建用户名为B访问ip为192.168.0.102的用户 密码为123123, 并给这个用户设置相应权限
B机配置
1.修改B机配置文件
[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock server-id=2 # 设置服务器编号必须唯一auto_increment_offset=2auto_increment_increment=2 gtid_mode=onenforce_gtid_consistency=onlog-bin=mysql-bin # slow queryslow_query_log=onslow_query_log_file=/var/lib/mysql/log/slow-query.log symbolic-links=0 log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid
2.在B机上创建一个用户,用于A机访问
create user A@'192.168.0.103' identified by '123456'; grant replication slave on *.* to A@'192.168.0.103';
注:创建用户名为A访问ip为192.168.0.103的用户 密码为123123, 并给这个用户设置相应权限
主从配置
如果只配置一台就变成了主从备份了
B机
1.在B机上进行,A主B从配置
change master to master_host='192.168.0.103', master_user='B', master_password='123123', master_port=3306, master_auto_position=1;
注:master_host为A机ip
2.启动主从
# 开启start slave;# 查看状态show slave status\G;
A机
1.在A机上进行,B主A从配置
change master to master_host='192.168.0.102', master_user='A', master_password='123456', master_port=3306, master_auto_position=1;
注:master_host为B机ip
2.启动主从
# 开启start slave;# 查看状态show slave status\G;