Mysql的主从同步
1.为什么要实现主从复制
2.环境的介绍
系统环境:centos7.0
客户端连接工具:xshell
远程文件传输工具:xftp
服务器:两台安装好jdk的linux服务器(一主 一从)
3.安装Mysql
基于rpm实现MySQL的安装
#查看有没有安装MySQL
rpm -qa | grep -i mysql
#卸载
rpm -e --nodeps mysql-community-client-5.7.28-1.el7.x86_64
#查找(查询全部文件 name中带有mysql的文件)
find / -name mysql
#删除存在的MySQL文件
rm -rf /etc/selinux/targeted/active/modules/100/mysql /usr/lib64/mysql /usr/share/mysql
#安装server
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm --force --nodeps
(force查看安装进度 nodeps查看安装条件)
#安装客户端
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm --force --nodeps
4.登录Mysql
# 登录命令
mysql -u root -p
# 出现报错
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
# 错误原因 密码错误
# 解决办法 免密登录
# 修改配置文件
vi /etc/my.cnf
# 在mysqlld下面 加上跳过授权代码
skip-grant-tables
#重启MySQL的服务
service mysqld restart
#继续登录
mysql -u root -p
Enter password:(这个地方不用输入任何东西直接回车)
#其实免密登录时不安全的,所以进来之后一定要设置密码
#刷新系统权限表
flush PRIVILEGES;
#重新设置密码
#alter user 'root'@'localhost' identified by '123456';
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
#刷新系统权限表
flush PRIVILEGES;
#退出mysql 将免密登录注释掉
exit;
5.实现Mysql 的主从复制
#先配置主服务器
#修改配置文件
vi /etc/my.cnf
#添加以下的内容
#日志文件的名字
log_bin=master-a-bin
#日志文件的格式
binlog-format=ROW
#服务器的id(zk的集群),一定要是唯一的
server-id=1
#对应需要实现主从复制的数据库
binlog_do_db=ddm
#添加完之后需要登录主服务器给从服务器授权
grant replication slave on *.* to 'root'@'服务器地址.%' identified by '123456';
#刷新系统权限表
flush PRIVILEGES;
#从服务器的配置
#修改配置文件
vi /etc/my.cnf
#添加以下的内容
#日志文件的名字
log_bin=master-a-bin
#日志文件的格式
binlog-format=ROW
#服务器的id(zk的集群),一定要是唯一的
server-id=2
#双主互相备份(表示从服务器可能是另外一台服务器的主服务器)
#log-slave-updates=true
然后我们发现我们的的mysql中并没有一个数据库 我们去创建一个数据库
#创建数据库
create database ddm;
#查询数据库
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ddm |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
#退出MySQL
mysql> exit;
配置完之后重启mysql服务器
service mysqlld restart;
6.验证主从复制
#登录主服务器
mysql -u root -p
#查看主服务器的状态
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| master-a-bin.000001 | 154 | ddm | | |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
#解释对应的一些名词
#File 生成的日志文件名
#Position 文件名所处的一个位置(偏移量)
#Binlog_Do_DB需要实现主从复制的数据库
#启动服务
systemctl mysqld restart
#登录从服务器
#设置从服务器如何找到主服务器
#设置主从复制的日志和偏移量
change master to master_host='主机ip',master_port=3306,master_user='root',master_password='123456',master_log_file='master-a-bin.000001',master_log_pos=154;
#启动slave的数据同步
start slave;
#停止slave的数据同步
stop slave;
#查看salve的配置信息
show slave status\G;
#这边还正在连接中
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.126.138
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-a-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-a-bin.000001
Slave_IO_Running: Connecting
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: 154
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'root@192.168.126.138:3306' - retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
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: 191119 20:48:42
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.00 sec)
ERROR:
No query specified
使用window系统测试mysql连接
#需要解决正在连接中的问题
#使用navicat客户端进行连接,发现连接不上
#1.防火墙的问题 2.端口未开放 3.未授权
#可以开放端口3306 需要退出mysql
firewall-cmd --zone=public --add-port=3306/tcp --permanent
#重新加载防火墙
firewall-cmd --reload
#还是没有将权限授予客户端
#with grant option 不仅仅是授予增删改查的权限,还授予权限的权限
grant all privileges on *.* to root@'%' identified by '123456' with grant option;
#授权报错
mysql> grant all privileges on *.* to root@'%' identified by '123456' with grant option;
ERROR 1819 (HY000): Unknown error 1819
1819密码不正确
#查看密码格式
mysql> select @@validate_password_policy;
+----------------------------+
| @@validate_password_policy |
+----------------------------+
| MEDIUM |
+----------------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.00 sec)
#修改mysql的密码格式
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_number_count=3;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=3;
Query OK, 0 rows affected (0.00 sec)
#再次查看密码状态
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 3 |
| validate_password_mixed_case_count | 0 |
| validate_password_number_count | 3 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 0 |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
#重新修改密码
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
Query OK, 0 rows affected (0.00 sec)
#刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#重新授权
mysql> grant all privileges on *.* to root@'%' identified by '123456' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
#刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#重启从服务器并且查看状态
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.126.138
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-a-bin.000001
Read_Master_Log_Pos: 1000
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 323
Relay_Master_Log_File: master-a-bin.000001
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: 1000
Relay_Log_Space: 1549
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: 1
Master_UUID: 0f900ff3-0ac6-11ea-aac7-000c299a89e6
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.00 sec)
ERROR:
No query specified
-
测试是否成功
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | youruike | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use youruike; Database changed mysql> show tables; Empty set (0.00 sec) mysql> create table user(id int primary key auto_increment,name varchar(20) not null)charset='utf8'; Query OK, 0 rows affected (0.12 sec) mysql> show tables; +---------------+ | Tables_in_youruike | +---------------+ | user | +---------------+ 1 row in set (0.00 sec) mysql> insert into user values(null,'a'); Query OK, 1 row affected (0.01 sec) mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | a | +----+------+ 1 row in set (0.00 sec) mysql> insert into user values(null,'b'); Query OK, 1 row affected (0.01 sec) mysql> insert into user values(null,'c'); Query OK, 1 row affected (0.00 sec) mysql> delete from user where id = 3; Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec)