湖南专业做网站公司/seo优化排名

本次安装环境：
OS版本：Red Hat Enterprise Linux Server release 6.8 (Santiago)
MySQL版本：5.7.42
架构：同一台机器，多实例安装搭建
ip地址：10.1.11.250
安装方式：采用MySQL二进制安装

下载MySQL二进制包

参考之前文章链接MySQL 8.0 安装
下载MySQL 5.7.42 安装包，唯一注意的点是glibc的版本。

[root@testbed opt]# rpm -qa|grep glibc      
glibc-2.12-1.192.el6.x86_64
glibc-headers-2.12-1.192.el6.x86_64
glibc-common-2.12-1.192.el6.x86_64
glibc-devel-2.12-1.192.el6.x86_64

glibc的版本是2.12。
官方的MySQL安装包的glibc版本只有一个，也是2.12，所以他适配的也就是rhel6。

操作系统环境配置

# groupadd mysql
# useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql
# cd /opt
# tar -zxvf mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz 
# cd /usr/local
# ln -s /opt/mysql-5.7.42-linux-glibc2.12-x86_64 mysql
# chown -R mysql:mysql /usr/local/mysql/
# mkdir -p /data/mysql/mysql3306/{data,logs,tmp}
# chown -R mysql:mysql /usr/local/mysql/
# chown -R mysql:mysql /data/mysql/mysql3306/

上传修改MySQL配置文件

[root@testbed mysql3306]# ls -ltr
total 20
-rw-r--r--. 1 mysql mysql 8129 Nov  5  2018 my3306.cnf
drwxr-xr-x. 2 mysql mysql 4096 Jun 25 09:11 tmp
drwxr-xr-x. 2 mysql mysql 4096 Jun 25 09:11 logs
drwxr-xr-x. 2 mysql mysql 4096 Jun 25 09:11 data
[root@testbed mysql3306]# cat my3306.cnf 
[client]
port            = 3306[mysql]
auto-rehash
prompt="\\u@\\h [\\d]>"
#pager="less -i -n -S"
#tee=/opt/mysql/query.log[mysqld]
####: for global
user                                =mysql                          #   mysql
basedir                             =/usr/local/mysql/              #   /usr/local/mysql/
datadir                             =/data/mysql/mysql3306/data     #   /usr/local/mysql/data
server_id                           =33306                        #     0
port                                =3306                           #   3306
character_set_server                =utf8                           #   latin1
explicit_defaults_for_timestamp     =off                            #    off
log_timestamps                      =system
default_time_zone                   ='+8:00'                   #        utc
socket                              =/tmp/mysql3306.sock                #       /tmp/mysql.sock
read_only                           = 1                             #   off
super_read_only                     = 1
skip_name_resolve                   =off                             #   0
auto_increment_increment            =1                              #   1
auto_increment_offset               =1                              #   1
lower_case_table_names              =1                              #   0
secure_file_priv                    =  /tmp/                         #  null
open_files_limit                    =65536                          #   1024
max_connections                     =1000                           #   151
thread_cache_size                   =64                             #   9
table_open_cache                    =81920                          #   2000
table_definition_cache              =4096                           #   1400
table_open_cache_instances          =64                             #   16
max_prepared_stmt_count             =1048576                        #####: for binlog
binlog_format                       =row                          #     row
log_bin                             =/data/mysql/mysql3306/logs/mysql-bin                      #        off
binlog_rows_query_log_events        =on                             #   off
log_slave_updates                   =on                             #   off
expire_logs_days                    =7                              #   0
binlog_cache_size                   =65536                          #   65536(64k)
#binlog_checksum                     =none                           #  CRC32
sync_binlog                         =1                              #   1
slave-preserve-commit-order         =ON                             #####: for error-log
log_error                           =error.log                        # /usr/local/mysql/data/localhost.localdomain.errgeneral_log                         =off                            #   off
general_log_file                    =general.log                    #   hostname.log####: for slow query log
slow_query_log                      =on                             #    off
slow_query_log_file                 =slow.log                       #    hostname.log
#log_queries_not_using_indexes       =on                             #    off
long_query_time                     =1.000000                       #    10.000000####: for gtid
#gtid_executed_compression_period    =1000                          #   1000
gtid_mode                           =on                            #    off
enforce_gtid_consistency            =on                            #    off####: for replication
skip_slave_start                     =1                              #
#master_info_repository              =table                         #   file
#relay_log_info_repository           =table                         #   file
slave_parallel_type                  =logical_clock                 #    database | LOGICAL_CLOCK
slave_parallel_workers               =4                             #    0
#rpl_semi_sync_master_enabled        =1                             #    0
#rpl_semi_sync_slave_enabled         =1                             #    0
#rpl_semi_sync_master_timeout        =1000                          #    1000(1 second)
#plugin_load_add                     =semisync_master.so            #
#plugin_load_add                     =semisync_slave.so             #
binlog_group_commit_sync_delay       =100                           #    500(0.05%秒)、默认值0
binlog_group_commit_sync_no_delay_count = 10                       #    0####: for innodb
innodb_data_file_path                           =ibdata1:100M:autoextend    #   ibdata1:12M:autoextend
innodb_temp_data_file_path                      =ibtmp1:12M:autoextend      #   ibtmp1:12M:autoextend
innodb_buffer_pool_filename                     =ib_buffer_pool             #   ib_buffer_pool
innodb_log_group_home_dir                       =./                         #   ./
innodb_log_files_in_group                       =3                          #   2
innodb_log_file_size                            =100M                       #   50331648(48M)
innodb_file_per_table                           =on                         #   on
innodb_online_alter_log_max_size                =128M                       #   134217728(128M)
innodb_open_files                               =65535                      #   2000
innodb_page_size                                =16k                        #   16384(16k)
innodb_thread_concurrency                       =0                          #   0
innodb_read_io_threads                          =4                          #   4
innodb_write_io_threads                         =4                          #   4
innodb_purge_threads                            =4                          #   4(垃圾回收)
innodb_page_cleaners                            =4                          #   4(刷新lru脏页)
innodb_print_all_deadlocks                      =on                         #   off
innodb_deadlock_detect                          =on                         #   on
innodb_lock_wait_timeout                        =20                         #   50
innodb_spin_wait_delay                          =128                          # 6
innodb_autoinc_lock_mode                        =2                          #   1
innodb_io_capacity                              =200                        #   200
innodb_io_capacity_max                          =2000                       #   2000
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc                        =on                         #   on
innodb_stats_persistent                         =on                         #   on
innodb_stats_persistent_sample_pages            =20                         #   20innodb_change_buffer_max_size                   =25                         #   25
innodb_flush_neighbors                          =1                          #   1
#innodb_flush_method                             =                           #
innodb_doublewrite                              =on                         #   on
innodb_log_buffer_size                          =128M                        #  16777216(16M)
innodb_flush_log_at_timeout                     =1                          #   1
innodb_flush_log_at_trx_commit                  =1                          #   1
innodb_buffer_pool_size                         =100M                  #        134217728(128M)
innodb_buffer_pool_instances                    =4
#--------innodb scan resistant
innodb_old_blocks_pct                           =37                         #    37
innodb_old_blocks_time                          =1000                       #    1000
#--------innodb read ahead
innodb_read_ahead_threshold                     =56                         #    56 (0..64)
innodb_random_read_ahead                        =OFF                        #    OFF
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct                     =25                         #    25
innodb_buffer_pool_dump_at_shutdown             =ON                         #    ON
innodb_buffer_pool_load_at_startup              =ON                         #    ON
innodb_flush_method                             = O_DIRECT

安装过程

数据库初始化

# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize

初始化日志：

[root@testbed data]# cat error.log 
2024-06-25T09:17:46.464009+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).100100100100
2024-06-25T09:17:47.233135+08:00 0 [Warning] InnoDB: New log files created, LSN=45790
2024-06-25T09:17:47.256665+08:00 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-06-25T09:17:47.330735+08:00 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: bb72a663-3290-11ef-bc60-000c29e3c118.
2024-06-25T09:17:47.332265+08:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-06-25T09:17:47.504191+08:00 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-06-25T09:17:47.504205+08:00 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-06-25T09:17:47.504620+08:00 0 [Warning] CA certificate ca.pem is self signed.
2024-06-25T09:17:47.565232+08:00 1 [Note] A temporary password is generated for root@localhost: Rcftzek;w80s

启动数据库

[root@testbed data]# ps -ef|grep mysql
root      27181   2151  0 09:20 pts/0    00:00:00 grep mysql
[root@testbed data]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[1] 27182
[root@testbed data]# ps -ef|grep mysql
mysql     27182   2151  6 09:20 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
root      27211   2151  0 09:20 pts/0    00:00:00 grep mysql

启动日志

2024-06-25T09:20:50.194162+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-06-25T09:20:50.196146+08:00 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2024-06-25T09:20:50.196174+08:00 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.42-log) starting as process 27182 ...
2024-06-25T09:20:50.202558+08:00 0 [Note] InnoDB: PUNCH HOLE support available
2024-06-25T09:20:50.202579+08:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2024-06-25T09:20:50.202583+08:00 0 [Note] InnoDB: Uses event mutexes
2024-06-25T09:20:50.202586+08:00 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2024-06-25T09:20:50.202590+08:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.13
2024-06-25T09:20:50.202593+08:00 0 [Note] InnoDB: Using Linux native AIO
2024-06-25T09:20:50.202603+08:00 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 4 to 1 since innodb_buffer_pool_size is less than 1024 MiB
2024-06-25T09:20:50.202947+08:00 0 [Note] InnoDB: Number of pools: 1
2024-06-25T09:20:50.203010+08:00 0 [Note] InnoDB: Using CPU crc32 instructions
2024-06-25T09:20:50.205724+08:00 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2024-06-25T09:20:50.210624+08:00 0 [Note] InnoDB: Completed initialization of buffer pool
2024-06-25T09:20:50.211465+08:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2024-06-25T09:20:50.228895+08:00 0 [Note] InnoDB: Highest supported file format is Barracuda.
2024-06-25T09:20:50.287124+08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2024-06-25T09:20:50.287250+08:00 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-06-25T09:20:50.293233+08:00 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2024-06-25T09:20:50.293682+08:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2024-06-25T09:20:50.293689+08:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2024-06-25T09:20:50.294077+08:00 0 [Note] InnoDB: Waiting for purge to start
2024-06-25T09:20:50.360419+08:00 0 [Note] InnoDB: 5.7.42 started; log sequence number 2766912
2024-06-25T09:20:50.363656+08:00 0 [Note] Plugin 'FEDERATED' is disabled.
2024-06-25T09:20:50.365561+08:00 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/mysql3306/data/ib_buffer_pool
2024-06-25T09:20:50.373689+08:00 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2024-06-25T09:20:50.373703+08:00 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2024-06-25T09:20:50.373711+08:00 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-06-25T09:20:50.373713+08:00 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-06-25T09:20:50.378248+08:00 0 [Warning] CA certificate ca.pem is self signed.
2024-06-25T09:20:50.378292+08:00 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2024-06-25T09:20:50.378514+08:00 0 [Note] Server hostname (bind-address): '*'; port: 3306
2024-06-25T09:20:50.386005+08:00 0 [Note] IPv6 is available.
2024-06-25T09:20:50.386028+08:00 0 [Note]   - '::' resolves to '::';
2024-06-25T09:20:50.386040+08:00 0 [Note] Server socket created on IP: '::'.
2024-06-25T09:20:50.404028+08:00 0 [Note] Event Scheduler: Loaded 0 events
2024-06-25T09:20:50.404302+08:00 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.42-log'  socket: '/tmp/mysql3306.sock'  port: 3306  MySQL Community Server (GPL)
2024-06-25T09:20:50.405925+08:00 0 [Note] InnoDB: Buffer pool(s) load completed at 240625  9:20:50

连接数据库

[root@testbed data]# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3306.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.42-logCopyright (c) 2000, 2023, Oracle and/or its affiliates.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.mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user user() identified by 'mysql';
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql>  set global super_read_only=off;
Query OK, 0 rows affected (0.00 sec)mysql> alter user user() identified by 'mysql';
Query OK, 0 rows affected (0.16 sec)

模拟有数据变化

mysql> create database test;
Query OK, 1 row affected (0.18 sec)mysql> use test;
Database changed
mysql> create table test (id int,name char);
Query OK, 0 rows affected (0.19 sec)mysql> insert into test values(1,'a');
Query OK, 1 row affected (0.15 sec)mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

搭建从库

环境准备，由于在同一台机器搭建，省略了安装软件的步骤

# mkdir -p /data/mysql/mysql3308/{data,logs,tmp}
# cp /data/mysql/mysql3306/my3306.cnf /data/mysql/mysql3308/my3308.cnf
# chown -R mysql:mysql /data/mysql/mysql3308/

批量修改配置文件：
:%s/3306/3308/g
sed -i ‘s/3306/3308/g’ my3308.cnf
主要就是port和server_id的修改，其余的可以和主库参数保持一致。
数据库初始化及启动

# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --initialize
# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3308.sock

主库备份

# /usr/local/mysql/bin/mysqldump -uroot -p -S /tmp/mysql3306.sock --master-data=2 --set-gtid-purged=OFF --single-transaction -A  >db3306-`date +%Y%m%d`.sql    

备份文件有如下内容，下面可以用到

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1062;

从库导入

# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3308.sock < db3306-20240625.sql

主库创建用户，及赋权

mysql> create user repl@'%' identified by 'repl';
Query OK, 0 rows affected (0.10 sec)mysql> grant replication slave on *.* to repl@'%';
Query OK, 0 rows affected (0.13 sec)mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

从库启动进程：

[root@testbed ~]# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3308.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.42-log MySQL Community Server (GPL)Copyright (c) 2000, 2023, Oracle and/or its affiliates.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.mysql> show slave status\G;
Empty set (0.00 sec)ERROR: 
No query specifiedmysql> change master to master_host='10.1.11.250', master_port=3306, master_user='repl', master_password='repl',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.1.11.250Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 1502Relay_Log_File: testbed-relay-bin.000003Relay_Log_Pos: 454Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1502Relay_Log_Space: 2224Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 33306Master_UUID: f1870083-32aa-11ef-9129-000c29e3c118Master_Info_File: /data/mysql/mysql3308/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: f1870083-32aa-11ef-9129-000c29e3c118:1-6Executed_Gtid_Set: 7dd629bf-32ab-11ef-9b8d-000c29e3c118:1-132,
f1870083-32aa-11ef-9129-000c29e3c118:1-6Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 
1 row in set (0.00 sec)

整个过程如下：

groupadd mysql
useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql
cd /opt
tar -zxvf mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz 
cd /usr/local
ln -s /opt/mysql-5.7.42-linux-glibc2.12-x86_64 mysql
chown -R mysql:mysql /usr/local/mysql/
mkdir -p /data/mysql/mysql3306/{data,logs,tmp}
chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /data/mysql/mysql3306/
sed -i 's/3311/3306/g' my3306.cnf/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &/usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3306.sockmkdir -p /data/mysql/mysql3308/{data,logs,tmp}
cp /data/mysql/mysql3306/my3306.cnf /data/mysql/mysql3308/my3308.cnf
chown -R mysql:mysql /data/mysql/mysql3308/
sed -i 's/3306/3308/g' my3308.cnf/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --initialize
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
/usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3308.sock/usr/local/mysql/bin/mysqldump -uroot -p -S /tmp/mysql3306.sock --master-data=2 --set-gtid-purged=OFF --single-transaction -A  >db3306-`date +%Y%m%d`.sql 
create user repl@'%' identified by 'repl';
grant replication slave on *.* to repl@'%';/usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3308.sock < db3306-20240625.sql
change master to master_host='10.1.11.250', master_port=3306, master_user='repl', master_password='repl',master_auto_position=1;