CentOS8 MySQL5.7主从备份(一台服务器)

本节的内容时基于上一篇文章的MySQL5.7多实例部署后，配置的主从备份。

上一篇文章地址CentOS8 MySQL5.7多实例部署_m0_70298159的博客-CSDN博客

开启MySQL服务，登陆到主库创建主从用户

create user'tigergao' identified by 'tigergao';

给用户赋予从机权限，如果有多台从机就执行多次

        注意：密码是有默认规则的，如果要去掉默认规则，参考我的安装MYSQL5的那篇文章

地址：CentOS8、Ubuntu安装mysql5.7,修改密码、配置远程访问、开机自启、创建开发者账户（超级详细）_m0_70298159的博客-CSDN博客

方式一

grant replication slave on *.* to 上一步创建的用户名称@'10.131.156.%' identified by '输入新的密码';

说明：10.131.156.%是你的服务器IP，%是所有的都可以。

方式二

GRANT REPLICATION SLAVE ON *.* TO '上一步创建的用户名称'@'10.131.156.101' IDENTIFIED BY '输入新的密码';

---

再主从库创建两张一样的表

打开/etc/my.cnf修改配置文件

红色标注的字体就是我的配置内容，大家可以直接复用

# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock #datadir=/usr/local/src/3306/data/mysql #socket=/usr/local/src/3306/data/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #服务器唯一标示，一般取服务器的ip server-id = 6 #启动MySQL二进制日志 log_bin = master-bin log_bin_index = master-bin.index #指定需要同步的数据库 binlog_do_db = test #指定不需要同步数据库 binlog_ignore_db = mysql [mysqld@3308] #服务器唯一标示，一般取服务器的ip server-id = 8 datadir=/var/lib/mysql3308 socket=/var/lib/mysql3308/mysql.sock innodb_flush_method=O_DIRECT innodb_file_per_table=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 user=mysql port=3308 log-error=/var/log/mysqld3308.log pid-file=/var/run/mysqld3308/mysqld.pid character-set-server=utf8 collation-server=utf8_general_ci explicit_defaults_for_timestamp=1

输入：show master status

以上就是主库的配置已经完成；

登陆从库

mysql -uroot -p -S /var/lib/mysql3308/mysql.sock

执行

change master to master_host='服务器IP',master_port=3306,master_user='主库用户',master_password='密码',master_log_file='master-bin.000002',master_log_pos=925;

背景高亮的地方如上图内容

 开启主从

reset slave;

查看sql线程和io线程是否成功应用

show slave status \G;

 

到此主从复制已经结束了！

验证主库添加数据从库是否复制，这里就不做演示了。

完毕 ！！！

下一篇文章讲述MySQL5.7主从的读写分离