centOS7 单机 mysql 5.7.24 主从 mysql主从命令

centOS7 单机 mysql 5.7.24 主从

@[toc]

背景

java 测试主从库多数据源切换，自己建个虚拟机数据库，随便造。

环境

• centos 7 x86_64
• mysql 5.7.24 x86_64 压缩版
• oracle vm virtualbox 5.2.16

安装

下载 mysql5.7 二进制压缩包

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

解压 mysql5.7 二进制包到指定目录

tar zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

建立 mysql 软件链接

ln -s /usr/local/mysql-5.7.24-linux-glibc2.12-x86_64 /usr/local/mysql 

创建 mysql 用户

useradd -r -s /sbin/nologin mysql

在 mysql 二进制包目录中创建 mysql-files 目录 [mysql 数据导入/导出数据专放目录]

mkdir -v /usr/local/mysql/mysql-files 

创建多实例数据目录

mkdir -vp /data/mysql_data{1..4} 

修改 mysql 二进制包目录的所属用户与所属组

chown root.mysql -R /usr/local/mysql-5.7.24-linux-glibc2.12-x86_64

修改 mysql 多实例数据目录与数据导入/导出专放目录的所属用户与所属组

chown mysql.mysql -R /usr/local/mysql/mysql-files /data/mysql_data{1..4}

配置 mysql 配置文件 /etc/my.cnf

[mysqld_multi] 
mysqld = /usr/local/mysql/bin/mysqld 
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /tmp/mysql_multi.log 
#user = root
#pass = 123456
[mysqld]
explicit_defaults_for_timestamp=true
skip-name-resolve
[mysqld1] 
# 设置数据目录　[多实例中一定要不同] 
datadir = /data/mysql_data1
# 设置sock存放文件名　[多实例中一定要不同] 
socket = /tmp/mysql.sock1 
# 设置监听开放端口　[多实例中一定要不同] 
port = 3306 
# 设置运行用户 
user = mysql 
# 关闭监控 
performance_schema = off 
# 设置innodb 缓存大小 
innodb_buffer_pool_size = 32M 
# 设置监听IP地址 
bind_address = 0.0.0.0 
# 关闭DNS 反向解析 
skip-name-resolve = 0 
#开启主从复制，主库的配置
#log-bin= mysql3306-bin
#指定主库serverid
#server-id=101
#指定同步的数据库，如果不指定则同步全部数据库
#binlog-do-db=zk_test
[mysqld2] 
datadir = /data/mysql_data2
socket = /tmp/mysql.sock2 
port = 3307 
user = mysql 
performance_schema = off 
innodb_buffer_pool_size = 32M 
bind_address = 0.0.0.0 
skip-name-resolve = 0 
#server-id=102
[mysqld3] 
datadir = /data/mysql_data3
socket = /tmp/mysql.sock3 
port = 3308 
user = mysql 
performance_schema = off 
innodb_buffer_pool_size = 32M 
bind_address = 0.0.0.0 
skip-name-resolve = 0 
#server-id=103
[mysqld4] 
datadir = /data/mysql_data4
socket = /tmp/mysql.sock4 
port = 3309 
user = mysql 
performance_schema = off 
innodb_buffer_pool_size = 32M 
bind_address = 0.0.0.0 
skip-name-resolve = 0
#server-id=104

初始化各个实例 [初始化完成后会自带随机密码在输出日志中]

# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1 
# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2 
# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3
# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4

复制多实例脚本到服务管理目录下 [ /etc/init.d/ ]

# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

添加脚本执行权限

# chmod +x /etc/init.d/mysqld_multi

添加进service服务管理

# chkconfig --add mysqld_multi 

mysqld_multi 多实例管理

查个多实例状态

[root@localhost tmp]# /etc/init.d/mysqld_multi report 
Reporting MySQL servers
MySQL server from group: mysqld1 is not running 
MySQL server from group: mysqld2 is not running 
MySQL server from group: mysqld3 is not running 
MySQL server from group: mysqld4 is not running

启动多实例

[root@localhost tmp]# /etc/init.d/mysqld_multi start

查看多实例状态

[root@localhost tmp]# /etc/init.d/mysqld_multi report 
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running

启动某个实例

[root@localhost tmp]# /etc/init.d/mysqld_multi start 1
[root@localhost tmp]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running

启动指定多个实例

[root@localhost tmp]# /etc/init.d/mysqld_multi start 1,2,3
[root@localhost tmp]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is not running

查看实例监听端口

[root@localhost tmp]# netstat -lntp | grep mysqld 
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 12048/mysqld 
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 12051/mysqld 
tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 12054/mysqld 
tcp 0 0 0.0.0.0:3309 0.0.0.0:* LISTEN 12057/mysqld

停止所有实例

[root@localhost data]# /etc/init.d/mysqld_multi stop

停止指定实例

[root@localhost tmp]# /etc/init.d/mysqld_multi start 1

停止指定多实例

[root@localhost tmp]# /etc/init.d/mysqld_multi start 1,2,3

该命令不作用，执行以下命令

mysql> grant shutdown on *.* to 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec) 

并将 my.cnf 配置文件，mysqld_multi 下的 user 和 pass 放开，使用 kill -9 杀死mysql 后，mysqld_multi stop 可用

[mysqld_multi] 
user = root
pass = 123456

此时在服务器上可以通过如下方式访问

/usr/local/mysql/bin/mysql -S /tmp/mysql.sock1 -p'!S+EwpgTk3lr'
/usr/local/mysql/bin/mysql -S /tmp/mysql.sock2 -p'XYg=enow9k=7'
/usr/local/mysql/bin/mysql -S /tmp/mysql.sock3 -p'thp9ydhtQk:f'
/usr/local/mysql/bin/mysql -S /tmp/mysql.sock4 -p';(9ti0?Zu19Y'

可能会报错

[root@localhost /]# /usr/local/mysql/bin/mysqld_multi start 1,2,3
WARNING: my_print_defaults command not found.
Please make sure you have this command available and
in your path. The command is available from the latest
MySQL distribution.
ABORT: Can't find command 'my_print_defaults'.
This command is available from the latest MySQL
distribution. Please make sure you have the command
in your PATH.

解决办法是输入如下命令

[root@localhost /]# #vim /etc/profile
export PATH=/usr/local/mysql/bin:$PATH

立即生效

source /etc/profile

修改 root 密码

安装完mysql 之后，登陆以后，不管运行任何命令，总是提示这个 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. ,由于初始化各实例的时候采用了系统生成的密码，不方便记忆和操作

mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

完成以上三步退出再登，使用新设置的密码就行了，以上除了123456修改成新密码外，其他原样输入即可

使用 ip 端口访问

添加软链接

ln -s /tmp/mysql.sock1 /tmp/mysql.sock

添加环境变量

[root@localhost /]# #vim /etc/profile
export PATH=/usr/local/mysql/bin:$PATH

可以通过以下方式访问

/usr/local/mysql/bin/mysql -h127.0.0.1 -uroot -p'123456' -P3306
/usr/local/mysql/bin/mysql -h127.0.0.1 -uroot -p'123456' -P3307
/usr/local/mysql/bin/mysql -h127.0.0.1 -uroot -p'123456' -P3308
/usr/local/mysql/bin/mysql -h127.0.0.1 -uroot -p'123456' -P3309

mysql 远程访问

开放3306、3307、3308、3309端口访问

CentOS 7.0默认使用的是firewall作为防火墙，这里改为iptables防火墙。

1.关闭firewall：

systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl mask firewalld.service

2.安装iptables防火墙

yum install iptables-services -y

3.启动设置防火墙

# systemctl enable iptables
# systemctl start iptables

4.查看防火墙状态

systemctl status iptables

5.编辑防火墙，增加端口

vi /etc/sysconfig/iptables #编辑防火墙配置文件
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3307 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3308 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3309 -j ACCEPT
:wq! #保存退出

6.重启配置，重启系统

systemctl restart iptables.service #重启防火墙使配置生效
systemctl enable iptables.service #设置防火墙开机启动

mysql修改远程访问权限

开启MySQL远程访问权限 允许远程连接

1.登陆mysql数据库

[root@localhost tmp]# /usr/local/mysql/bin/mysql -S /tmp/mysql.sock2 -p'123456'
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 6
Server version: 5.7.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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.

2.查看user表

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 host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.00 sec) 

可以看到在user表中已创建的root用户。host字段表示登录的主机，其值可以用IP，也可用主机名，

(1)有时想用本地IP登录，那么可以将以上的Host值改为自己的Ip即可。

3.实现远程连接(授权法)

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

将权限改为ALL PRIVILEGES

mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> grant all privileges on *.* to root@'%' identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+---------------+
3 rows in set (0.00 sec)

这样机器就可以以用户名root密码root远程访问该机器上的MySql.

4.实现远程连接（改表法）

use mysql;
update user set host = '%' where user = 'root';

这样在远端就可以通过root用户访问Mysql.

主从同步

主库配置

在my.cnf修改：

#开启主从复制，主库的配置
log-bin= mysql3306-bin
#指定主库serverid
server-id=101
#指定同步的数据库，如果不指定则同步全部数据库
binlog-do-db=zk_test

查询主库状态

执行SQL语句查询状态：需要记录下Position值，需要在从库中设置同步起始值。

mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql3306-bin.000004 | 154 | test | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在主库创建同步用户

#授权用户slave01使用123456密码登录mysql
mysql> grant replication slave on *.* to 'slave01'@'127.0.0.1'identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

从库配置

在my.cnf修改：

#指定serverid，只要不重复即可，从库也只有这一个配置，其他都在SQL语句中操作
server-id=102

重启所有服务

[root@localhost tmp]# /etc/init.d/mysqld_multi stop

[root@localhost tmp]# /etc/init.d/mysqld_multi start

在从库执行以下SQL：

mysql> change master to
 master_host='127.0.0.1',
 master_user='slave01',
 master_password='123456',
 master_port=3306,
 master_log_file='mysql3306-bin.000004',
 master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
#启动slave同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
#查看同步状态 Slave_IO_Running: Yes Slave_SQL_Running: Yes 这两个都是 Yes 就说明同步设置成功
mysql> show slave status\G;
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 127.0.0.1
 Master_User: slave01
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql3306-bin.000006
 Read_Master_Log_Pos: 1072
 Relay_Log_File: localhost-relay-bin.000002
 Relay_Log_Pos: 324
 Relay_Master_Log_File: mysql3306-bin.000006
 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: 1072
 Relay_Log_Space: 535
 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: 101
 Master_UUID: d2131ead-fd22-11e8-9613-0800277ea7e8
 Master_Info_File: /data/mysql_data3/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

测试主从数据同步

创建数据库 zk_test

执行数据库脚本

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for zk_user
-- ----------------------------
DROP TABLE IF EXISTS `zk_user`;
CREATE TABLE `zk_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of zk_user
-- ----------------------------
INSERT INTO `zk_user` VALUES ('1', '张三');

可以看到从库，从建库到建表插入数据，操作跟主库是一致的

数据库从某一位置进行数据同步

如果，中间进行数据同步，需要将之前数据导入到从库，再开启从库同步，需要重新查看主库的 show master status; 里面 File 、 Position 字段的值，Position 是从哪一步开始重新同步

change master to
 master_host='127.0.0.1',
 master_user='slave01',
 master_password='123456',
 master_port=3306,
 master_log_file='mysql3306-bin.000006',
 master_log_pos=1072;

从库提供只读权限账号

查看 SHOW GLOBAL VARIABLES LIKE 'read_only'

设置只读

解决方法

mysql> set global read_only=1; 
Query OK, 0 rows affected (0.00 sec)
#set global read_only=0 为取消普通账号的只读模式

授权普通MySQL测试账号,只可以进行读

mysql> grant select on zk_test.* to 'test'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

赋值具体权限时可以这样

grant select on testdb.* to common_user@'%'
grant insert on testdb.* to common_user@'%'
grant update on testdb.* to common_user@'%'
grant delete on testdb.* to common_user@'%

多个权限时可以

grant select,insert,update,delete on testdb.* to common_user@'%'

参考地址

Mysql 5.7.21单机多实例安装

mysqld_multi stop无效问题

mysql error You must reset your password using ALTER USER statement before executing this statement.