mysql5.7安装:
1.查看已安装的MariaDB相关rpm包。
rpm -qa|grep mariadb
2.移除已安装的MariaDB相关yum包，包名需根据yum list命令的结果判断。此步骤需要root权限。
yum remove mariadb-libs
3.获取mysql5.7.20的rpm安装包
4.解压安装包
[root@localhost mysql]# ls
mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar
[root@localhost mysql]# tar -xvf mysql-5.7.20-1.el6.x86_64.rpm-bundle.tar
mysql-community-embedded-devel-5.7.20-1.el6.x86_64.rpm
mysql-community-common-5.7.20-1.el6.x86_64.rpm
mysql-community-client-5.7.20-1.el6.x86_64.rpm
mysql-community-test-5.7.20-1.el6.x86_64.rpm
mysql-community-server-5.7.20-1.el6.x86_64.rpm
mysql-community-devel-5.7.20-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.20-1.el6.x86_64.rpm
mysql-community-libs-5.7.20-1.el6.x86_64.rpm
mysql-community-embedded-5.7.20-1.el6.x86_64.rpm
5.按照步骤来安装就好
1.rpm -ivh mysql-community-libs-5.7.20-1.el6.x86_64.rpm --nodeps --force
2.rpm -ivh mysql-community-devel-5.7.20-1.el6.x86_64.rpm --nodeps --force
3.rpm -ivh mysql-community-client-5.7.20-1.el6.x86_64.rpm --nodeps --force
4.rpm -ivh mysql-community-server-5.7.20-1.el6.x86_64.rpm --nodeps --force
6.修改密码 设置mysql验证密码跳过
vi /etc/my.cnf
[mysqld]# 下添加设置：
skip-grant-tables=1 #不用验证
7.启动mysql
/etc/init.d/mysqld start |本机默认地址 //开启mysql
8.进入并修改mysql登录密码:
1. mysql -uroot -p;
2.use mysql;
3.update user set authentication_string = password('root'), password_expired = 'N', password_last_changed = now() where user = 'root';
4.exit;
9.删除 my.cnf 下 skip-grant-tables=1 #不用验证 这一行配置
10.重启mysql
[root@localhost mysql]# /etc/init.d/mysqld restart
11.重登录并设置mysql远端访问用户:
1.mysql -uroot -proot;
2. update user set host = '%' where user = 'root';
3.在 /etc/my.cnf 下修改my.cnf 添加地址放行
bind-address = 0.0.0.0
4.重启mysql
[root@localhost mysql]# /etc/init.d/mysqld restart
12.关闭防火墙并使用navicat进行连接
systemctl stop firewalld.service
mysql主从同步配置:
1.配置主Mysql服务器，修改my.cnf
命令: vim /etc/my.cnf
配置内容:
server-id=1 #服务器id (主从必须不一样)
log-bin=mysql-bin #存储日志的位置
binlog-do-db=tmall #要给从机同步的库
#binlog-do-db= binlog-ignore-db=mysql #不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7 #自动清理 7 天前的log文件,可根据需要修改
2.创建备份账号与测试log_bin是否成功开启
show variables like '%log_bin%';
查看log_bin是否开启成功:
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.01 sec)
创建备份账户backup 并设置密码 123456 :
grant replication slave on *.* to 'backup'@'%' identified by '123456';
设置过程中报错:
mysql> grant replication slave on *.* to 'backup'@'%' identified by '123456';
ERROR 1819 (HY000): Unknown error 1819
原因以及解决办法:
mysql默认设置密码是八位复杂型的，简单密码设置不通过。
解决办法：
1.首先，修改validate_password_policy参数的值 即policy = 0 ，仅限制密码的长度
set global validate_password_policy=0;
2.查看默认密码的长度
select @@validate_password_length;
3.修改默认密码的长度（这里修改为4）
set global validate_password_length=4;
重新执行如上步骤:
grant replication slave on *.* to 'backup'@'%' identified by '123456';
mysql> grant replication slave on *.* to 'backup'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
3.查看主机配置状态，查看快照:
show master status\G
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 431
Binlog_Do_DB: tmall
Binlog_Ignore_DB: information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
4.关闭主数据库的读取锁定
unlock tables;
5.查看备份 用户创建状态
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| backup | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | % |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
6.设置从Mysql服务器
编辑 位置 : vim /etc/my.conf
设置参数:
[mysqld]
basedir=/usr/local/mysql #mysql路径
datadir=/data/mysqldata #mysql数据目录
socket=/tmp/mysql.sock
user=mysql
server_id=2 #MySQLid 后面2个从服务器需设置不同
port=3306
#加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错。
read_only = 1
master_info_repository=TABLE
relay_log_info_repository=TABLE
#relay_log_recovery=1 #从机禁止写 后续打开
#super_read_only=1 #从机禁止读 后续打开
创建目录: mkdir -p /data/mysqldata
7.重启 mysql服务：
service mysqld restart
8.登录slave数据库，并做如下设置
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.160.129', #服务Mysql主机地址
master_user='backup', #账户
master_password='123456', #密码
master_log_file='mysql-bin.000001' , #从前面主机部分查看 保持一致
master_log_pos=431; #从前面主机部分查看 保持一致
Query OK, 0 rows affected, 2 warnings (0.34 sec)
mysql> start slave;
Query OK, 0 rows affected (0.29 sec)
9.登录Mysql 查看配置情况和快照:
[root@localhost ~]# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.160.129
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 5952
Relay_Log_File: localhost-relay-bin.000023
Relay_Log_Pos: 1313
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes #开启IO复制线程
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: 5952
Relay_Log_Space: 3005
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: 90b0ad1a-9ceb-11e9-b7f7-000c29cde100
Master_Info_File: mysql.slave_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)
10.进行查看验证主从复制配置
在主数据库创建tmall数据库，并在从数据库创建tmall 数据库，主数据库创建表。然后
修改从数据库配置 ，编辑命令 vim /etc/my.cnf ,讲相关配置打开
relay_log_recovery=1 #从机禁止写 后续打开
super_read_only=1 #从机禁止读 后续打开
11.重启从数据库mysql
service mysqld restart
12.登录从数据库，使用 show slave status \G 查看配置状况：
确保以下配置都处于启用状态:
Slave_IO_Running: Yes #开启IO复制线程
Slave_SQL_Running: Yes #是否在启用
问题:如果 显示 Slave_SQL_Running: no #是否在启用 ,需要在登录状态下 修改相关配置 ：
配置如下:
mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;
本文暂时没有评论，来添加一个吧(●'◡'●)