centos7 测试socket_centOS7 单机 mysql 5.7.24 主从

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/mysqladminlog = /tmp/mysql_multi.log #user = root#pass = 123456[mysqld]explicit_defaults_for_timestamp=trueskip-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_data2socket = /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_data3socket = /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_data4socket = /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 serversMySQL 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 serversMySQL server from group: mysqld1 is runningMySQL server from group: mysqld2 is runningMySQL server from group: mysqld3 is runningMySQL server from group: mysqld4 is running

启动某个实例

[root@localhost tmp]# /etc/init.d/mysqld_multi start 1[root@localhost tmp]# /etc/init.d/mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld1 is runningMySQL server from group: mysqld2 is not runningMySQL server from group: mysqld3 is not runningMySQL 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 reportReporting MySQL serversMySQL server from group: mysqld1 is runningMySQL server from group: mysqld2 is runningMySQL server from group: mysqld3 is runningMySQL 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 = rootpass = 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,3WARNING: my_print_defaults command not found.Please make sure you have this command available andin your path. The command is available from the latestMySQL distribution.ABORT: Can't find command 'my_print_defaults'.This command is available from the latest MySQLdistribution. Please make sure you have the commandin your PATH.

解决办法是输入如下命令

[root@localhost /]# #vim /etc/profileexport 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/profileexport 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.servicesystemctl disable firewalld.servicesystemctl 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 6Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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: 0mysql> 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#指定主库serveridserver-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密码登录mysqlmysql> 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 statusG;*************************** 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: 0Master_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.