mysql应对高并发，主从数据库配置

geniusbluesky

已于 2022-05-31 08:04:07 修改

阅读量396

点赞数

分类专栏： mysql 文章标签： mysql 数据库服务器

于 2022-05-29 22:49:30 首次发布

本文链接：https://blog.csdn.net/geniusbluesky/article/details/125037525

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1. 两台数据库服务器安装同一版本的mysql

安装过程略

2.也可以在同一服务器使用不同端口安装同一版本的mysql

centos7下安装和配置多个mysql实例

3.配置主服务器Master

修改my.cnf文件，在[mysqld]加入下面的内容：

[mysqld]
skip-grant-tables
port=3307

# 服务的唯一编号
server-id= 1
# 开启mysql binlog功能
log-bin= mysql-bin
# binlog记录内容的方式，记录被操作的每一行
binlog_format= ROW
# 减少记录日志的内容，只记录受影响的列
binlog_row_image= minimal
# 指定需要复制的数据库名为test,主库从库必须一样
binlog-do-db= test

basedir=/home/mysqlMultiple/mysql_3307/mysql-5.7.38-linux-glibc2.12-x86_64/
datadir=/home/mysqlMultiple/mysql_3307/mysql-5.7.38-linux-glibc2.12-x86_64/data/
socket=/home/mysqlMultiple/mysql_3307/mysql-5.7.38-linux-glibc2.12-x86_64/mysql.sock

max_connect_errors=10

character-set-server=utf8

default-storage-engine=INNODB

user=mysql

slow_query_log=on

slow_query_log_file=/home/mysqlMultiple/mysql_3307/mysql-5.7.38-linux-glibc2.12-x86_64/slow-query.log

[client]

port=3307

socket=/home/mysqlMultiple/mysql_3307/mysql-5.7.38-linux-glibc2.12-x86_64/mysql.sock
default-character-set=utf8

修改好配置文件，重启mysql服务

servicemysql restart

3.创建从库同步数据的账号
登录mysql客户端：

./mysql -u root –p

然后添加一个用户【用于在从库主机中登录进行同步】:

create user 'replicate'@'%' identified by 'replicate';

再对该用户授予复制权限：

grant replication slave on *.* to 'replicate'@'%';

使执行生效：

flush privileges;

查看主库状态

show master status\G;

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 1732
     Binlog_Do_DB: test
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

需要注意的是：如果没有上面第3步操作，执行show master status \G;会报错，如下图：

file：表示同步的bin-log信息从哪个文件开始；

position：表示从file的哪个位置开始；

binlog_do_db：表示同步哪一个库

在后面的slave配置中，这三个结果都需要使用到。

4.配置从服务器slave

修改my.cnf文件，在[mysqld]加入下面的内容：

[mysqld]
skip-grant-tables
port=3308

server-id= 2

log-bin= mysql-bin
binlog_format= ROW
binlog_row_image= minimal
replicate-do-db= test

basedir=/home/mysqlMultiple/mysql_3308/mysql-5.7.38-linux-glibc2.12-x86_64/


datadir=/home/mysqlMultiple/mysql_3308/mysql-5.7.38-linux-glibc2.12-x86_64/data/


socket=/home/mysqlMultiple/mysql_3308/mysql-5.7.38-linux-glibc2.12-x86_64/mysql.sock

max_connect_errors=10

character-set-server=utf8

default-storage-engine=INNODB

user=mysql

slow_query_log=on

slow_query_log_file=/home/mysqlMultiple/mysql_3308/mysql-5.7.38-linux-glibc2.12-x86_64/slow-query.log

[client]

port=3308

socket=/home/mysqlMultiple/mysql_3308/mysql-5.7.38-linux-glibc2.12-x86_64/mysql.sock
default-character-set=utf8

修改好配置文件，重启mysql服务

servicemysql restart

执行同步命令
给从设置主服务器ip，同步账号密码，同步位置

mysql的终端执行:

change master to master_host='127.0.0.1',master_port=3307,master_user='replicate',master_password='replicate',master_log_file='mysql-bin.000002',master_log_pos=1732;

命令需要特别注意：master_log_file 和master_log_pos 2个属性内容，这2个属性是刚才主服务器上执行show master status\G;查询出来的内容。

开启从机角色的

start slave;

查看从库的状态
mysql的终端执行:

show slaves tatus\G;

返回信息为

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: replicate
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1080
               Relay_Log_File: iZ2ze55u1570vkg325gowxZ-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          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: 1080
              Relay_Log_Space: 545
              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: a3aa59eb-df29-11ec-8bfe-00163e00624f
             Master_Info_File: /home/mysqlMultiple/mysql_3308/mysql-5.7.38-linux-glibc2.12-x86_64/data/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)

当：Slave_IO_Running和Slave_SQL_Running的状态都为Yes时，说明从库配置成功。

5.验证

首先在主库上创建test库:

create database test character set ‘utf8’;

依次执行：

use test;

show tables;

主库此时仍然没有表，查看从库

依次执行上述3条命令：

show databases;

use test;

show tables;

查询可知：slave节点的test库同样为空。

此时可以证明，主库的修改(创建了test)成功同步到从库中了。

接着在master节点上创建表people。

create table people(id int ,name varchar(20)) character set ‘utf8’;

show tables;

接着在slave上验证：

发现数据已经进行了同步。

至此，mysql的主备已经搭建完毕。

6.拓展

问题1：上述介绍的是备份某个库的操作，如果需要备份多个库则直接在master的my.cnf中添加新的一行binlog-do-db=test_2等等即可。这行代码的意思是指：同步该库（test_2）的bin-log信息。

问题2：这种直接修改master节点的my.cnf文件去同步某个库，需要重启master才能让整个mysql集群工作，有没有一种方法可以搭建备库，但是不需要重启master？

显然是可以的，不过这次备份的库是master的全部库了（因为没有指定具体库的bin-log），所以默认就把全部的bin-log都同步了。可以参考如下的步骤：

step 1：导出Master节点中所有的数据库

mysqldump -uroot -A --routines --single-transaction --master-data=2 > /data1/mysqldump/alldatabases.sql

参数注释：

-A：导出所有库的数据

–routines Dump stored routines (procedures and functions) from dumped databases

–single-transaction Issue a BEGIN SQL statement before dumping data from server

–master-data Write the binary log file name and position to the output

这里添加option：–master-data之后，就可以不再使用show master status\G;去查看bin-log的file和position了。

step 2：

将上述的sql文件导入到备库中。

step 3：

其余步骤同上述内容【在master上创建一个用户，并授权，然后刷新】；在slave上执行change操作。

参考文章

mysql_install_db --basedir=/usr --datadir=/home/mysql/mysql_3307/data --user=mysql

mysql_install_db --basedir=/usr --datadir=/home/multiMysql/datadir/3307 --user=mysql



mysqld --initialize --basedir=/usr --datadir=/home/multiMysql/datadir/3307 --user=mysql


mysqld  --defaults-file=/home/mysql/mysql_3307/my.cnf  &


chown -R mysql:mysql /home/mysqlMultiple/mysql_3307/ 

chown -R mysql:mysql /home/mysqlMultiple/mysql_3308/

./bin/mysqld --defaults-file=/home/mysqlMultiple/mysql_3307/mysql-5.7.38-linux-glibc2.12-x86_64/my.cnf --initialize --console --user=mysql


./bin/mysqld --defaults-file=/home/mysqlMultiple/mysql_3308/mysql-5.7.38-linux-glibc2.12-x86_64/my.cnf --initialize --console --user=mysql



socket=/home/mysqlMultiple/mysql_3307/mysql-5.7.38-linux-glibc2.12-x86_64/mysql.sock

explicit_defaults_for_timestamp=on


./bin/mysql --socket=./mysql.sock -uroot -p uq;_p&sV)9GM


alter user root@localhost identified by "jnyz1@3.";

alter user user() identified by "jnyz1@3.";

grant all privileges on *.* to root@'%'identified by 'jnyz1@3.';


change master to master_host='127.0.0.1',master_port=3307,master_user='replicate',master_password='replicate',master_log_file='mysql-bin.000002',master_log_pos=1732;


show slave status\G;