centOS7 单机 mysql 5.7.24 主从

墨蓝 2022-03-22 13:09 235阅读 0赞

## centOS7 单机 mysql 5.7.24 主从 ##

### 文章目录 ###

*   *  centOS7 单机 mysql 5.7.24 主从
     *   *  背景
         *  环境
         *  安装
         *   *  下载 mysql5.7 二进制压缩包
             *  解压 mysql5.7 二进制包到指定目录
             *  建立 mysql 软件链接
             *  创建 mysql 用户
             *  在 mysql 二进制包目录中创建 mysql-files 目录 \[mysql 数据导入/导出数据专放目录\]
             *  创建多实例数据目录
             *  修改 mysql 二进制包目录的所属用户与所属组
             *  修改 mysql 多实例数据目录与数据导入/导出专放目录的所属用户与所属组
             *  配置 mysql 配置文件 /etc/my.cnf
             *  初始化各个实例 \[初始化完成后会自带随机密码在输出日志中\]
             *  复制多实例脚本到服务管理目录下 \[ /etc/init.d/ \]
             *  添加脚本执行权限
             *  添加进service服务管理
             *  mysqld\_multi 多实例管理
         *  修改 root 密码
         *  使用 ip 端口访问
         *  mysql 远程访问
         *   *  开放3306、3307、3308、3309端口访问
             *   *  1.关闭firewall：
                 *  2.安装iptables防火墙
                 *  3.启动设置防火墙
                 *  4.查看防火墙状态
                 *  5.编辑防火墙，增加端口
                 *  6.重启配置，重启系统
             *  mysql修改远程访问权限
             *  1.登陆mysql数据库
             *  2.查看user表
             *  3.实现远程连接(授权法)
             *  4.实现远程连接（改表法）
         *  主从同步
         *   *  主库配置
             *   *  在my.cnf修改：
                 *  查询主库状态
                 *  在主库创建同步用户
             *  从库配置
             *   *  在my.cnf修改：
             *  重启所有服务
             *  在从库执行以下SQL：
             *  测试主从数据同步
             *  数据库从某一位置进行数据同步
         *  从库提供只读权限账号
         *  参考地址

### 背景 ###

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表 ####

可以看到在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值，需要在从库中设置同步起始值。

##### 在主库创建同步用户 #####

#授权用户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单机多实例安装][Mysql 5.7.21]

[mysqld\_multi stop无效问题][mysqld_multi stop]

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

[Mysql 5.7.21]: http://www.mamicode.com/info-detail-2234538.html
[mysqld_multi stop]: https://www.cnblogs.com/qizhelongdeyang/p/6292966.html
[mysql error You must reset your password using ALTER USER statement before executing this statement.]: https://www.cnblogs.com/debmzhang/p/5013540.html