做网站怎么弄模板,wordpress建站图片效果,wordpress 仿,网页设计培训学校哪家好MySQL 5.7.42 主从复制环境搭建 下载MySQL二进制包操作系统环境配置安装过程搭建从库 本次安装环境#xff1a; OS版本#xff1a;Red Hat Enterprise Linux Server release 6.8 (Santiago) MySQL版本#xff1a;5.7.42 架构#xff1a;同一台机器#xff0c;多实例安装搭… MySQL 5.7.42 主从复制环境搭建 下载MySQL二进制包操作系统环境配置安装过程搭建从库 本次安装环境 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的版本。
[roottestbed 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_64glibc的版本是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配置文件
[roottestbed 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
[roottestbed mysql3306]# cat my3306.cnf
[client]
port 3306[mysql]
auto-rehash
prompt\\u\\h [\\d]
#pagerless -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初始化日志
[roottestbed data]# cat error.log
2024-06-25T09:17:46.46400908: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.23313508:00 0 [Warning] InnoDB: New log files created, LSN45790
2024-06-25T09:17:47.25666508:00 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-06-25T09:17:47.33073508: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.33226508:00 0 [Warning] Gtid table is not ready to be used. Table mysql.gtid_executed cannot be opened.
2024-06-25T09:17:47.50419108:00 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-06-25T09:17:47.50420508:00 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-06-25T09:17:47.50462008:00 0 [Warning] CA certificate ca.pem is self signed.
2024-06-25T09:17:47.56523208:00 1 [Note] A temporary password is generated for rootlocalhost: Rcftzek;w80s启动数据库
[roottestbed data]# ps -ef|grep mysql
root 27181 2151 0 09:20 pts/0 00:00:00 grep mysql
[roottestbed data]# /usr/local/mysql/bin/mysqld --defaults-file/data/mysql/mysql3306/my3306.cnf
[1] 27182
[roottestbed 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.19416208: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.19614608: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.19617408:00 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.42-log) starting as process 27182 ...
2024-06-25T09:20:50.20255808:00 0 [Note] InnoDB: PUNCH HOLE support available
2024-06-25T09:20:50.20257908:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2024-06-25T09:20:50.20258308:00 0 [Note] InnoDB: Uses event mutexes
2024-06-25T09:20:50.20258608:00 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2024-06-25T09:20:50.20259008:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.13
2024-06-25T09:20:50.20259308:00 0 [Note] InnoDB: Using Linux native AIO
2024-06-25T09:20:50.20260308: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.20294708:00 0 [Note] InnoDB: Number of pools: 1
2024-06-25T09:20:50.20301008:00 0 [Note] InnoDB: Using CPU crc32 instructions
2024-06-25T09:20:50.20572408:00 0 [Note] InnoDB: Initializing buffer pool, total size 100M, instances 1, chunk size 100M
2024-06-25T09:20:50.21062408:00 0 [Note] InnoDB: Completed initialization of buffer pool
2024-06-25T09:20:50.21146508: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.22889508:00 0 [Note] InnoDB: Highest supported file format is Barracuda.
2024-06-25T09:20:50.28712408:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2024-06-25T09:20:50.28725008:00 0 [Note] InnoDB: Setting file ./ibtmp1 size to 12 MB. Physically writing the file full; Please wait ...
2024-06-25T09:20:50.29323308:00 0 [Note] InnoDB: File ./ibtmp1 size is now 12 MB.
2024-06-25T09:20:50.29368208:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2024-06-25T09:20:50.29368908:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2024-06-25T09:20:50.29407708:00 0 [Note] InnoDB: Waiting for purge to start
2024-06-25T09:20:50.36041908:00 0 [Note] InnoDB: 5.7.42 started; log sequence number 2766912
2024-06-25T09:20:50.36365608:00 0 [Note] Plugin FEDERATED is disabled.
2024-06-25T09:20:50.36556108:00 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/mysql3306/data/ib_buffer_pool
2024-06-25T09:20:50.37368908: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.37370308:00 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2024-06-25T09:20:50.37371108:00 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-06-25T09:20:50.37371308:00 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-06-25T09:20:50.37824808:00 0 [Warning] CA certificate ca.pem is self signed.
2024-06-25T09:20:50.37829208:00 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2024-06-25T09:20:50.37851408:00 0 [Note] Server hostname (bind-address): *; port: 3306
2024-06-25T09:20:50.38600508:00 0 [Note] IPv6 is available.
2024-06-25T09:20:50.38602808:00 0 [Note] - :: resolves to ::;
2024-06-25T09:20:50.38604008:00 0 [Note] Server socket created on IP: ::.
2024-06-25T09:20:50.40402808:00 0 [Note] Event Scheduler: Loaded 0 events
2024-06-25T09:20:50.40430208: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.40592508:00 0 [Note] InnoDB: Buffer pool(s) load completed at 240625 9:20:50连接数据库
[roottestbed 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_onlyoff;
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-data2 --set-gtid-purgedOFF --single-transaction -A db3306-date %Y%m%d.sql 备份文件有如下内容下面可以用到 -- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000002, MASTER_LOG_POS1062;
从库导入
# /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 rootlocalhost |
---------------------------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO rootlocalhost WITH GRANT OPTION |
| GRANT PROXY ON TO rootlocalhost WITH GRANT OPTION |
---------------------------------------------------------------------
2 rows in set (0.00 sec)从库启动进程
[roottestbed ~]# /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_host10.1.11.250, master_port3306, master_userrepl, master_passwordrepl,master_auto_position1;
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-data2 --set-gtid-purgedOFF --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_host10.1.11.250, master_port3306, master_userrepl, master_passwordrepl,master_auto_position1;
