- 查看数据库是否正常启动
# 查看启动状态
systemctl status mysql.service
# 停止MySQL服务
systemctl status mysql.service
# 启动MySQL服务
systemctl status mysql.service
- 登录
MySQL
,并设置root
账户允许远程连接
# 登录MySQL
mysql -uroot -p123456
# 切换mysql数据库
use mysql;
# 修改允许使用root账户连接的ip为任意
update user set host='%' where user='root';
# 刷新权限
flush privileges;
-
无法远程连接
MySQL
-
检查配置文件
- 查看端口开放情况
netstat -an | grep 3306 tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
- 开放端口并重新启动
# 编辑MySQL配置 vi /etc/mysql/mysql.conf.d/mysqld.cnf #注释掉这行 # bind-address = 127.0.0.1 # 重新启动MySQL service mysql restart # 查看端口监听状态 netstat -an | grep 3306 tcp6 0 0 :::3306 :::* LISTEN
-
检查防火墙
# 查看防火墙状态 systemctl status firewalld # 关闭防火墙 systemctl stop firewalld # 开启防火墙 systemctl start firewalld
-
1.2、主机配置
修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf
vi /etc/mysql/mysql.conf.d/mysqld.cnf
具体配置如下:
# 主机配置-主机配置
# 主服务唯一ID
server-id=1
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
# 设置 logbin 格式
binlog_format=STATEMENT
binlog
日志三种格式
STATEMENT
ROW
MIXED
1.3、从机配置
修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf
vi /etc/mysql/mysql.conf.d/mysqld.cnf
具体配置如下:
# 主机配置-从机配置
# 从服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay
1.4、注意事项
- 主机、从机配置好后需要重启
MySQL
服务
# 查看启动状态
systemctl status mysql.service
# 停止MySQL服务
systemctl stop mysql.service
# 启动MySQL服务
systemctl start mysql.service
# 重启MySQL服务
systemctl restart mysql.service
- 主机、从机需要关闭防火墙
# 查看防火墙状态
systemctl status firewalld
# 关闭防火墙
systemctl stop firewalld
# 开启防火墙
systemctl start firewalld
- 在主机上建立账户并授权
slave
# 登录MySQL
mysql -uroot -p123456
# 切换数据库
use mysql;
#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
1.5、主从配置
- 登录主机,查询
master
的状态
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 | 430 | testdb | mysql,information_schema | |
+------------------+-----