mysql8 安装教程
参考
https://cloud.tencent.com/developer/article/2234226?areaSource=102001.12&traceId=gxY-7iiL7ozARGndEhYaj
卸载
rpm -qa | grep -i mariadb
rpm -e --nodeps mariadb-libs-5.5.64-1.el7.x86_64
再次确认卸载完毕
rpm -qa | grep mysql
mkdir /usr/local/my
cd /usr/local/my
wget https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm
yum -y install mysql80-community-release-el7-7.noarch.rpm
yum -y install mysql-community-server
启动mysql之前配置表名大小写
参考
https://zhuanlan.zhihu.com/p/147720463
vim /etc/my.cnf
lower_case_table_names=1
启动mysql
systemctl start mysqld.service
查看初始化密码
cat /var/log/mysqld.log | grep password
修改root密码
alter user 'root'@'localhost' identified by 'root123Asssd.123';
设置远程访问
use mysql;
update user set host = '%' where user = 'root';
flush privileges;
mysql 主备搭建
按照以上教程搭建好两台mysql,记得关闭防火墙,或者让3306通过
192.168.9.101 主
192.168.9.102 备
主配置----------------------开始
vim /etc/my.cnf
server-id=1 # 服务器id (设置唯一标识)
binlog-do-db=test # 要给从机同步的库 多个用空格隔开
binlog-ignore-db=mysql # 不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
log-bin=mysql-bin # 打开日志(主机需要打开),可以指定绝对路径;
expire_logs_days=90 # 自动清理 90 天前的log文件,可根据需要修改
重启mysql
service mysqld restart
创建同步账号 192.168.9.102 是备库的ip
CREATE USER 'repl'@'192.168.9.102' IDENTIFIED WITH mysql_native_password BY 'repl123Asssd.123';
GRANT REPLICATION SLAVE,replication client ON *.* TO 'repl'@'192.168.9.102';
flush privileges;
如有问题可以 drop user repl@‘192.168.9.102’
查看主库状态 记录File和Position
show master status \G
备份主库
mysqldump -uroot -proot123Asssd.123 --all-databases > /usr/local/my/mysql.sql
主配置----------------------结束
备库配置-------------------开始
vim /etc/my.cnf
server-id=2 # MySQLid 后面2个从服务器需设置不同
skip_slave_start=1 # 复制进程不会随着数据库的启动而启动,重启数据库后需手动启动;
#加上以下参数可以避免更新不及时,SLAVE 重启后导致的主从复制出错。
read_only=1 # 从库普通账户只读;
master_info_repository=TABLE
relay_log_info_repository=TABLE
slave-skip-errors=1032,1062,1007
#relay_log_recovery=1 # 从机禁止写
#super_read_only=1 # 从机禁止写
重启备库
service mysqld restart
还原主库的备份
source /usr/local/my/mysql.sql
配置同步 192.168.9.101 是主库ip mysql-bin.000003 和 157 是 在主库里面看到的 通过查看 show master status \G
CHANGE MASTER TO MASTER_HOST='192.168.9.101', MASTER_USER='repl', MASTER_PASSWORD='repl123Asssd.123', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=157;
开启同步
start slave
show slave status \G
显示 slave_io_running YES 和 slave_sql_running YES 表示没问题
可以试试能不能同步了
如果之前开启过主备,碰到问题需要重置同步配置
Could not initialize connection metadata structure; more error messages can be found in the MySQL error log
reset slave;
参考 https://blog.csdn.net/beyondlpf/article/details/46429417