mysql-5.7主从同步安装

CentOS 6.5/6.6 安装（install）mysql 5.7 最完整版教程

https://segmentfault/a/1190000003049498

Step1: 检测系统是否自带安装mysql

# yum list installed | grep mysql
Step2: 删除系统自带的mysql及其依赖
命令：

# yum -y remove mysql-libs.x86_64
Step3: 给CentOS添加rpm源，并且选择较新的源
命令：

# wget dev.mysql/get/mysql-community-release-el6-5.noarch.rpm
# yum localinstall mysql-community-release-el6-5.noarch.rpm
# yum repolist all | grep mysql
# yum-config-manager --disable mysql55-community
# yum-config-manager --disable mysql56-community
# yum-config-manager --enable mysql57-community-dmr
# yum repolist enabled | grep mysql
Step4:安装mysql 服务器
命令：

# yum install mysql-community-server
Step5: 启动mysql
命令:

# service mysqld start
Step6: 查看mysql是否自启动,并且设置开启自启动
命令:

# chkconfig --list | grep mysqld
# chkconfig mysqld on
Step7: mysql安全设置
命令：

# mysql_secure_installation

chown -R mysql:mysql /home/mysql

vi /etc/myf  #改变安装路径
datadir=/home/mysql




在CentOS7上使用yum安装MySQL 5.7        #https://blog.frognew/2017/05/yum-install-mysql-5.7.html

CentOS 7.3 192.168.61.100
disable selinux

setenforce 0

vi /etc/selinux/config
SELINUX=disabled
yum安装MySQL
安装mysql yum repository

wget https://repo.mysql//mysql57-community-release-el7-11.noarch.rpm

yum localinstall mysql57-community-release-el7-11.noarch.rpm
确认yum repository已经安装

yum repolist enabled | grep "mysql.*-community.*"
mysql-connectors-community/x86_64       MySQL Connectors Community           36
mysql-tools-community/x86_64            MySQL Tools Community                47
mysql57-community/x86_64                MySQL 5.7 Community Server          187
查看MySQL版本：

yum repolist all | grep mysql


安装：

yum install mysql-community-server
启动和配置MySQL服务
修改MySQL配置文件/etc/myf

datadir=/home/mysql

server_id = 1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-storage-engine=INNODB
#Optimize omit
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log-bin     = binlog
log_bin_trust_function_creators=1
binlog_format = ROW
expire_logs_days = 99
sync_binlog = 0
slow-query-log=1
slow-query-log-file=/var/log/mysql/slow-queries.log
long_query_time = 3
log-queries-not-using-indexes
explicit_defaults_for_timestamp = 1
启动MySQL服务：

systemctl start mysqld
systemctl status mysqld
查看生成mysql root用户临时密码：

grep 'temporary password' /var/log/mysqld.log
修改root用户的密码:

mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';


mysql5.7忘记密码时，修改root密码
在mysqld进程配置文件中添加skip-grant-tables,添加完成后,执行:wq保存。 

mysql -uroot -p 
UPDATE mysql.user SET authentication_string=PASSWORD("123456") WHERE user='root' and Host = 'localhost';
flush privileges;
quit;
密码修改完成后，将myf文件中添加的skip-grant-tables语句注释或删除掉，然后重启数据库即可


修改临时密码
Mysql5.7默认安装之后root是有密码的。
grep 'temporary password' /var/log/mysqld.log

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123';  修改密码

如果密码设置太简单出现以下的提示
如何解决ERROR 1819 (HY000): Your password does not satisfy the current policy requirements呢
必须修改两个全局参数： 
首先，修改validate_password_policy参数的值

mysql> set global validate_password_policy=0; 
1
再修改密码的长度

set global validate_password_length=1;

mysql>update user set host = '%' where user = 'root';
1
再次执行修改密码就可以了

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123';


菜鸟级修改密码
https://blog.csdn/u012323576/article/details/73863283
一,停止mysql 进程，service mysqld stop  
二，mysqld_safe --user=mysql --skip-grant-tables --skip-networking & 
三， #mysql 
#use mysql 
update mysql.user set authentication_string=password('liujia') where user='root' ; 
四,重启mysql service mysqld restart

mysql 二进制通用安装
https://dev.mysql/downloads/mysql/5.7.html 页面查看
cd /root
wget http://dev.mysql/get/Downloads/MySQL-5.7/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz

# 解压
tar xvfz mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz

# 移动
mv mysql-5.7.29-linux-glibc2.12-x86_64 /usr/local/mysql
cd /usr/local/mysql

# 补全一些目录
mkdir /usr/local/mysql/data
mkdir /var/log/mysql

# 创建用户
useradd mysql

# 修改权限
chown -R mysql.mysql /usr/local/mysql

# 安装依赖,否则报 libaio.so.1 找不到
yum -y install libaio*

# 安装
/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --initialize




注：在执行完此条命令后 mysql 会自动生成登录时初始密码，需要您记下来。
vi /etc/myf

[mysqld]
character-set-server=utf8
user=mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
log_warnings
log-output=file
general_log=0
slow-query-log=1
long_query_time=1
# 取消密码验证
skip-grant-tables
###InnodbSetting
innodb_buffer_pool_size=16384M
innodb_log_file_size=512M
innodb_log_buffer_size=16M
innodb_log_files_in_group=3
innodb_flush_log_at_trx_commit=1
innodb_stats_on_metadata=OFF
#innodb_thread_concurrency=16
innodb_flush_method=O_DIRECT
log-bin=mysql-bin
binlog_format=mixed
max_binlog_size=1024M
max_binlog_cache_size=4096M
binlog_stmt_cache_size=1M
table_open_cache=4096
binlog_cache_size=4M
expire_logs_days=30
relay_log_purge=0
sort_buffer_size=2M

server_id=1001
report_host=202.155.226.4

lower_case_table_names=1
innodb_print_all_deadlocks
skip_external_locking
skip_name_resolve
#read_only=1

max_allowed_packet=32M
max_connections=1000
max_connect_errors=10000
#interactive_timeout=600
#wait_timeout=600
#event_scheduler=on

sql_mode=NO_ENGINE_SUBSTITUTION
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

innodb_lru_scan_depth=256


快捷方式和自动重启
# 添加快捷方式
ln -s /usr/local/mysql/bin/mysql /usr/bin
ln -s /usr/local/mysql/bin/mysqldump /usr/bin

# 将mysql加入服务
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

# 开机自启
chkconfig mysql on

# 开启
systemctl start mysql

# 进入MySQL，设置密码
/usr/local/mysql/bin/mysql -u root -p

重设密码
开始MySQL操作
use mysql;
update user set authentication_string=password('root') where user='root';
flush privileges;
exit;
调整配置文件 /etc/myf
将/etc/myf中的skip-grant-tables删除

vi /etc/myf

:%s/skip-grant-tables/# skip-grant-tables/g
:wq

/usr/local/mysql/bin/mysql -u root -proot
登录再次设置一次
 
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
exit;

systemctl restart mysql
创建一个非 root 账号，允许远程连接
/usr/local/mysql/bin/mysql -u root -proot
-- 创建业务数据库
CREATE DATABASE ${db_name} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建一个远程查询的的账号
CREATE USER ${user_name} IDENTIFIED BY '${user_password}';
-- 授权所有 IP 可以访问
GRANT SELECT ON ${db_name}.* TO '${user_name}'@'%';

-- 创建一个局域网访问的账号
CREATE USER '${local_user_name}'@'192.168.0.%' IDENTIFIED BY '${local_user_password}';

GRANT ALL ON ${db_name}.* TO '${local_user_name}'@'192.168.0.%';
-- 刷新用户权限
flush privileges;


设置主从同步

1、停止主库的数据更新操作
mysql>flush tables with read lock;
2、新开终端，生成主数据库的备份（导出数据库）
[root@zhoujietest ~]# mysqldump -uroot -proot student > student.sql
3、将备份文件传到从库
[root@zhoujietest ~]# scp student.sql root@192.168.1.109:/root/
4、主库解锁
mysql>unlock tables;

mysql -uroot -proot student<student.sql 

怎么安装mysql数据库，这里不说了，只说它的主从复制，步骤如下：

1、主从服务器分别作以下操作：
  1.1、版本一致
  1.2、初始化表，并在后台启动mysql
  1.3、修改root的密码

2、修改主服务器master:
   #vi /etc/myf
       [mysqld]
       log-bin=mysql-bin   //[必须]启用二进制日志
       server-id=222      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

3、修改从服务器slave:
   #vi /etc/myf
       [mysqld]
       log-bin=mysql-bin   //[不是必须]启用二进制日志
       server-id=226      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

4、重启两台服务器的mysql
   /etc/init.d/mysql restart

5、在主服务器上建立帐户并授权slave:

grant replication slave on *.* to 'repl'@'192.168.1.%' identified by 'liujia'

6、登录主服务器的mysql，查询master的状态
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      691 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

7、配置从服务器Slave：

stop slave;
reset slave; 重置master服务,删除旧同步日志

mysql>change master to master_host='192.168.1.70',master_user='repl',master_password='liujia',master_log_file='mysql-bin.000003',master_log_pos=691;

 Mysql>start slave;    //启动从服务器复制功能

8、检查从服务器复制功能状态：
mysql> show slave status;
Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.2.222  //主服务器地址
              Master_User: mysync   //授权帐户名，尽量避免使用root
              Master_Port: 3306    //数据库端口，部分版本没有此行
              Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 600     //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos
              Relay_Log_File: ddte-relay-bin.000003
              Relay_Log_Pos: 251
              Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes    //此状态必须YES
              Slave_SQL_Running: Yes     //此状态必须YES
以上操作过程，主从服务器配置完成。

MySQL同步故障：" Slave_SQL_Running:No" 两种解决办法  

解决办法一、

Slave_SQL_Running: No
1.程序可能在slave上进行了写操作

2.也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成的：
解决办法：
mysql>  stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

 

解决办法二、

首先停掉Slave服务：slave stop
到主服务器上查看主机状态：
记录File和Position对应的值

进入master

mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000094 | 33622483 |              |                  | 
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 

然后到slave服务器上执行手动同步：

mysql> change master to 
> master_host='master_ip',
> master_user='user', 
> master_password='pwd', 
> master_port=3306, 
> master_log_file=localhost-bin.000094', 
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)

myf精简配置

[mysqld]
basedir=/data/mysql/mysql5.7/
datadir=/data/mysql/mysql_data
socket=/tmp/mysql.sock
user=mysql
symbolic-links=0
lower_case_table_names=1
# 允许最大连接数
max_connections=300
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
max_allowed_packet=16M

[client]
port=3306

[mysqld_safe]
log-error=/data/mysql/log/mysqld.log

实现远程连接(授权法)

将host字段的值改为%就表示在任何客户端机器上能以root用户登录到mysql服务器，建议在开发时设为%。
update user set host = '%' where user = 'root';