目录

文章目录

目录MySQL主从复制主机(window)修改my.ini配置文件从机(centos7.x)修改my.cnf配置文件主机和从机重启后台mysql服务主机和从机都关闭防火墙在Window主机上建立账户并授权slave在Linux从机上配置需要复制的主机

MySQL主从复制

主机(window)修改my.ini配置文件

# The TCP/IP Port the MySQL Server will listen on port=3306 #主服务器唯一ID server-id=1 #启用二进制日志 log-bin="E:\mysql\log\mysql-bin" # Error Logging. log-error="E:\mysql\error\mysql-err" #主机可读可写 read-only=0 #设置不需要复制的数据库 binlog-ignore-db=mysql #设置需要复制的数据库 binlog-do-db=xxx

从机(centos7.x)修改my.cnf配置文件

vim /etc/my.cnf #Linux默认开启二进制日志文件 /var/lib/mysql/binlog Binary logging captures changes between backups and is enabled by default. It's default setting is log_bin=binlog #启用从服务器唯一ID server-id=2

主机和从机重启后台mysql服务

net stop mysql80; net start mysql80; systemctl restart mysqld;

主机和从机都关闭防火墙

systemctl stop firewalld;

在Window主机上建立账户并授权slave

GRANT REPLICATION SLAVE ON *.* TO '从库主机用户名'@'从库主机IP' IDENTIFIED BY '从库主机密码'; flush privileges; #查看主库状态 show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 155 | | mysql | | +------------------+----------+--------------+------------------+-------------------+

在Linux从机上配置需要复制的主机

CHANGE MASTER TO MASTER_HOST = '主库主机IP', MASTER_USER = 'root', MASTER_PASSWORD = 'xxxx', #File 加引号 MASTER_LOG_FILE = 'mysql-bin.000001', #Position 不加引号 MASTER_LOG_POS = 155; #启动从服务器复制功能 start slave; #查看从库状态 show slave status \G; #主从配置成功 Slave_IO_Running: Yes Slave_SQL_Running: Yes #查看中继日志是否开启 show global variables like '%log%'; #查看节点的server id show global variables like '%server%';