mysql实现主从配置

主ip:192.168.0.250
从ip:192.168.0.251


步骤:

主服务器master:

1.部署数据库,从数据库版本可以比主服务器高
rpm -ivh * --nodeps --force
2.创建备份数据库和数据表 MYSQL 简单的建库操作代码参考:https://www.cnblogs.com/chengzhongde/p/6957086.html
create database test character set utf8;
use test;
create table tb_mobile( mobile VARCHAR(20) comment'手机号码', time timestamp DEFAULT now() comment'时间' );

3.修改mysql配置文件(/etc/my.cnf)
[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id

4.创建备份数据库用户,和授予权限 参考:https://www.cnblogs.com/idlo/p/10872324.html
CREATE USER 'admin'@'从服务器ip地址' IDENTIFIED BY 'test'; #创建用户
GRANT REPLICATION SLAVE ON *.* TO 'admin'@'从服务器ip地址'; #分配权限
flush privileges;#刷新权限

(刷新可能会报错,提示ERROR 1146 (42S02): Table 'mysql.servers' doesn't exist

解决办法:
use mysql;
CREATE TABLE `servers` (
`Server_name` char(64) NOT NULL,
`Host` char(64) NOT NULL,`Db` char(64) NOT NULL,
`Username` char(64) NOT NULL,
`Password` char(64) NOT NULL,
`Port` int(4) DEFAULT NULL,
`Socket` char(64) DEFAULT NULL,
`Wrapper` char(64) NOT NULL,
`Owner` char(64) NOT NULL,
PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table';

5.重启mysql服务 service mysqld restart

6.查看master状态,记录二进制文件名(mysql-bin.000002)和位置(154):

SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+


从服务器slave:

1.修改mysql配置文件(/etc/my.cnf)
[mysqld]
server-id=2 #设置server-id

2.重启mysql,打开mysql会话,执行同步SQL语句(需要主服务器主机名,登陆凭据,二进制文件的名称和位置):

service mysqld restart ;

CHANGE MASTER TO
MASTER_HOST='192.168.0.250', 主服务器ip
MASTER_USER='admin',
MASTER_PASSWORD='test',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154;

3.启动slave同步进程:
start slave;
stop slave;

4.查看slave状态:
show slave status\G;


出现的问题:

1.Mysql主从同步时Slave_IO_Running:Connecting ; Slave_SQL_Running:Yes的情况故障排除

可能原因:
1.网络不通
2.账户密码错误
3.防火墙
4.mysql配置文件问题
5.连接服务器时语法
6.主服务器mysql权限

解决办法:
参考:https://blog.csdn.net/mbytes/article/details/86711508

2.MySQL数据同步,出现Slave_SQL_Running:no和slave_io_running:no问题的解决方法

解决办法:
参考:https://www.cnblogs.com/l-hh/p/9922548.html

3.其他问题解决方法

解决办法:
参考:https://blog.csdn.net/xiaozhenzi66/article/details/81220624

4.MySQL重置root密码,刷新权限表报错ERROR 1146(42S02):Table ‘mysql.servers’ doesn`t exist
解决办法:
参考:https://www.cnblogs.com/Mchn/p/9984186.html

5.参考文档:
https://www.cnblogs.com/gl-developer/p/6170423.html
https://www.cnblogs.com/lelehellow/p/9633315.html
https://www.cnblogs.com/lay2017/p/9043985.html
https://www.cnblogs.com/fengff/p/10255711.html

posted @ 2020-08-17 13:39  陈程宸  阅读(144)  评论(0编辑  收藏  举报