模式识别,残情首席我不要,奇人奇案演员表
实现目标
搭建两台mysql服务器(一主一从),一台作为主服务器,一台作为从服务器,主服务器进行写操作,从服务器进行读操作。
工作流程概述
准备工作
开始配置
[mysqld] log-bin=mysql-bin #开启二进制日志 server-id=201 #设置server-id,唯一值,标识主机
systemctl restart mysqld
mysql -u root -p
回车输入密码。#创建用户(ip为可访问该master的ip,任意ip就写'%') mysql> create user 'master_root'@'192.168.244.202' identified by 'mysql@6688'; #分配权限(ip为可访问该 master的ip,任意ip就写'%') mysql> grant replication slave on *.* to 'master_root'@'192.168.244.202'; #刷新权限 mysql>flush privileges;
show master status;
vim /etc/my.cnf
[mysqld] server-id=202 #设置server-id,唯一值,唯一标识从库
systemctl restart mysqld
mysql> change master to -> master_host='192.168.244.201', -> master_user='master_root', -> master_password='mysql@6688', -> master_log_file='mysql-bin.000001', -> master_log_pos=154;
mysql>start slave;
show slave status\g
error: no query specified
mysql> show slave status\g *************************** 1. row *************************** slave_io_state: waiting for master to send event master_host: 192.168.244.201 master_user: master_root master_port: 3306 connect_retry: 60 master_log_file: mysql-bin.000002 read_master_log_pos: 154 relay_log_file: localhost-relay-bin.000007 relay_log_pos: 320 relay_master_log_file: mysql-bin.000002 slave_io_running: yes slave_sql_running: yes replicate_do_db: replicate_ignore_db: replicate_do_table: replicate_ignore_table: replicate_wild_do_table: replicate_wild_ignore_table: last_errno: 0 last_error: skip_counter: 0 exec_master_log_pos: 154 relay_log_space: 531 until_condition: none until_log_file: until_log_pos: 0 master_ssl_allowed: no master_ssl_ca_file: master_ssl_ca_path: master_ssl_cert: master_ssl_cipher: master_ssl_key: seconds_behind_master: 0 master_ssl_verify_server_cert: no last_io_errno: 0 last_io_error: last_sql_errno: 0 last_sql_error: replicate_ignore_server_ids: master_server_id: 201 master_uuid: 7dd766bb-f005-11e9-81ba-000c29a69f1b master_info_file: /var/lib/mysql/master.info sql_delay: 0 sql_remaining_delay: null slave_sql_running_state: slave has read all relay log; waiting for more updates master_retry_count: 86400 master_bind: last_io_error_timestamp: last_sql_error_timestamp: master_ssl_crl: master_ssl_crlpath: retrieved_gtid_set: executed_gtid_set: auto_position: 0 replicate_rewrite_db: channel_name: master_tls_version: 1 row in set (0.01 sec)
我也不知道为啥我这里这么长一段,网上的教程都是好短一段。管他呢,反正到这也对了。
当slave_io_running和slave_sql_running都为yes的时候就表示主从同步设置成功了。
特别注意:
2019-10-16t12:59:09.987976z 1 [error] slave i/o for channel '': fatal error: the slave i/o thread stops because master and slave have equal mysql server uuids; these uuids must be different for replication to work. error_code: 1593
接下来就可以进行一些验证了,比如在主master数据库的test数据库的一张表中插入一条数据,在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效,还可以关闭slave(mysql>stop slave;),然后再修改master,看slave是否也相应修改(停止slave后,master的修改不会同步到slave),就可以完成主从复制功能的验证了。
还可以用到的其他相关参数:
master开启二进制日志后默认记录所有库所有表的操作,可以通过配置来指定只记录指定的数据库甚至指定的表的操作,具体在mysql配置文件的[mysqld]可添加修改如下选项:
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
binlog-do-db = game
如之前查看master状态时就可以看到只记录了test库,忽略了manual和mysql库。
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
小白安装登录mysql-8.0.19-winx64的教程图解(新手必看)
Navicat连接MySQL时报10060、1045错误及my.ini位置问题
网友评论