主库在192.168.5.179为ubuntu操作系统 (本机)
从库在192.168.2.34为centos操作系统 (服务器,位于机房)
1.配置主库
vim /etc/mysql/mysql.conf.d/mysqld.cnf 配置master
添加
skip-grant-tables#(可选当远程机子不能登录此mysqls时,设置用于测试)
server-id=1
log-bin=mysql-bin
binlog-do-db=job
binlog_ignore_db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performance_schema
binlog_ignore_db = sys
重启mysql
登录mysql
新建用户用于同步数据库
grant replication slave on *.* to 'test'@'192.168.5.179' identified by '123456'
show master status;
2.配置从库
vim /etc/my.cnf
添加
symbolic-links=1 #可选,从0改为1,从库配置了启动不能成功时可配置测试)
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2
log-bin=mysql-bin
#master-host=192.168.5.179(不能配置,配置了master-开头的参数,导致了从库不能启动)
#master-user=test
#master-password=MyNewPass4!
#master-password=123456
#master-port=3306
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-do-db=job
重启mysql
登录mysql后运行命令,其中master_log_file,master_log_pos通过主库中show master status;查看
change master to master_host='192.168.5.179',master_port=3306,master_user='test',master_password='123456',master_log_file='mysql-bin.000008',master_log_pos=154,master_connect_retry=60;
stop slave;
start slave;
show slave status\G
查看显示下面就表示slave启动成功
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
把两边库删除重新 create database job后同步成功
一些命令:
本机重启mysql
service mysql restart
service mysql stop
service mysql start
192.168.2.34
mysql安装https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html
下载rpm文件后
sudo yum localinstall mysql57-community-release-el7-10.noarch.rpm
sudo yum install mysql-community-server
sudo service mysqld start
sudo service mysqld status
sudo grep 'temporary password' /var/log/mysqld.log 查看初始密码用于登录
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
flush privileges;
重启mysql
service mysqld restart
service mysqld stop
service mysqld start
vim /etc/my.cnf 配置slave
show master status;
stop slave;
start slave;
show slave status\G