由 SuKai December 2, 2024
- 安装Percona Server Mysql
- 配置基于GTID的主从复制
安装Percona Server Mysql
[rocky@sukai01 ~]$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
For example, to enable the Percona Distribution for MySQL 8.0 repository use:
percona-release setup pdps8.0
Complete!
[rocky@sukai01 ~]$
[rocky@sukai01 ~]$ sudo percona-release setup ps80
* Disabling all Percona Repositories
* Enabling the Percona Server for MySQL 8.0 repository
* Enabling the Percona Tools repository
<*> All done!
[rocky@sukai01 ~]$
[rocky@sukai01 yum.repos.d]$ sudo dnf -y module disable mysql
Last metadata expiration check: 0:07:09 ago on Fri 29 Nov 2024 01:13:18 PM UTC.
Dependencies resolved.
======================================================================================================================================
Package Architecture Version Repository Size
======================================================================================================================================
Disabling modules:
mysql
Transaction Summary
======================================================================================================================================
Complete!
[rocky@sukai01 ~]$ sudo yum install -y percona-server-server percona-toolkit percona-xtrabackup-80
[rocky@sukai01 ~]$ sudo mysql_secure_installation
mysql_secure_installation: [ERROR] unknown option '--no-auto-rehash'.
Securing the MySQL server deployment.
Enter password for user root:
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
[rocky@sukai01 ~]$
配置open_files_limit
[rocky@sukai01 ~]$ sudo vi /etc/systemd/system/multi-user.target.wants/mysqld.service
# Sets open_files_limit
LimitNOFILE = Infinity
[rocky@sukai01 ~]$ sudo systemctl daemon-reload
[rocky@sukai01 ~]$ sudo systemctl start mysql
配置基于GTID的主从复制
主数据库创建Replication用户
create user 'databasereplication'@'%' identified by '88888888';
grant replication slave on *.* to 'databasereplication'@'%';
flush privileges;
从数据库配置Replication
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.10.143',SOURCE_USER='databasereplication',SOURCE_PASSWORD='88888888',SOURCE_AUTO_POSITION=1,GET_SOURCE_PUBLIC_KEY=1;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. |
| Note | 1760 | Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> start replica;
Query OK, 0 rows affected (0.09 sec)
mysql> show replica status;
+----------------------------------+-------------+-------------+-------------+---------------+-----------------+---------------------+----------------------------+---------------+-----------------------+--------------------+---------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------------------------------------------------------------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Replica_IO_State | Source_Host | Source_User | Source_Port | Connect_Retry | Source_Log_File | Read_Source_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Source_Log_File | Replica_IO_Running | Replica_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Source_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Source_SSL_Allowed | Source_SSL_CA_File | Source_SSL_CA_Path | Source_SSL_Cert | Source_SSL_Cipher | Source_SSL_Key | Seconds_Behind_Source | Source_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Source_Server_Id | Source_UUID | Source_Info_File | SQL_Delay | SQL_Remaining_Delay | Replica_SQL_Running_State | Source_Retry_Count | Source_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Source_SSL_Crl | Source_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Source_TLS_Version | Source_public_key_path | Get_Source_public_key | Network_Namespace |
+----------------------------------+-------------+-------------+-------------+---------------+-----------------+---------------------+----------------------------+---------------+-----------------------+--------------------+---------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------------------------------------------------------------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Waiting for source to send event | 192.168.10.143 | databasereplication | 3306 | 60 | bin-log.000007 | 197 | sukai02-relay-bin.000002 | 369 | bin-log.000007 | Yes | Yes | | | | | | | 0 | | 0 | 197 | 583 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | 05d98455-aef8-11ef-94ca-d2b98de6614b | mysql.slave_master_info | 0 | NULL | Replica has read all relay log; waiting for more updates | 86400 | | | | | | | 05d98455-aef8-11ef-94ca-d2b98de6614b:1-10,
162a6554-af03-11ef-b8de-6a5af150e235:1-3 | 1 | | | | | 1 | |
+----------------------------------+-------------+-------------+-------------+---------------+-----------------+---------------------+----------------------------+---------------+-----------------------+--------------------+---------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------------------------------------------------------------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
1 row in set (0.00 sec)
mysql>
配置主数据库文件my.cnf
[rocky@sukai01 ~]$ more /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
secure_log_path = /var/log/mysql
pid-file = /var/run/mysqld/mysqld.pid
datadir = /data/mysql
log-error = /var/log/mysql/mysql.log
server-id = 1
default-authentication-plugin = mysql_native_password
#authentication_policy = caching_sha2_password
back_log = 50
max_connections = 2048
wait_timeout = 256
max_connect_errors = 10
skip-name-resolve
table_open_cache = 2048
max_allowed_packet = 16M
max_heap_table_size = 512M
read_buffer_size = 64M
read_rnd_buffer_size = 64M
sort_buffer_size = 64M
join_buffer_size = 64M
thread_cache_size = 8
thread_stack = 240K
ft_min_word_len = 4
default-storage-engine = InnoDB
transaction_isolation = REPEATABLE-READ
tmp_table_size = 512M
slow_query_log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
log_slow_admin_statements = ON
log_slow_replica_statements = ON
log_slow_rate_limit=100
log_slow_rate_type='query'
slow_query_log_always_write_time=1
log_slow_verbosity='full'
slow_query_log_use_global_control='all'
# INNODB options
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 1GB
innodb_change_buffering = all
innodb_change_buffer_max_size = 25
#innodb_log_file_size = 125M
#innodb_log_files_in_group = 2
innodb_redo_log_capacity= 250M
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 256
innodb_file_per_table
default_time_zone = +00:00
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-statements-digest=ON
innodb_monitor_enable=all
#skip-log-bin
log_bin = /data/mysql/bin-log
log_bin_index = /data/mysql/bin-log.index
read-only = 0
#binlog_format = STATEMENT
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
gtid_mode = on
enforce_gtid_consistency = on
[mysqldump]
quick
max_allowed_packet = 50M
[mysql]
no-auto-rehash
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
配置从数据库文件my.cnf
[rocky@sukai02 ~]$ sudo cat /etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
secure_log_path = /var/log/mysql
pid-file = /var/run/mysqld/mysqld.pid
datadir = /data/mysql
log-error = /var/log/mysql/mysql.log
server-id = 144
default-authentication-plugin = mysql_native_password
#authentication_policy = caching_sha2_password
back_log = 50
max_connections = 2048
wait_timeout = 256
max_connect_errors = 10
skip-name-resolve
table_open_cache = 2048
max_allowed_packet = 16M
max_heap_table_size = 512M
read_buffer_size = 64M
read_rnd_buffer_size = 64M
sort_buffer_size = 64M
join_buffer_size = 64M
thread_cache_size = 8
thread_stack = 240K
ft_min_word_len = 4
default-storage-engine = InnoDB
transaction_isolation = REPEATABLE-READ
tmp_table_size = 512M
slow_query_log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
log_slow_admin_statements = ON
log_slow_replica_statements = ON
log_slow_rate_limit=100
log_slow_rate_type='query'
slow_query_log_always_write_time=1
log_slow_verbosity='full'
slow_query_log_use_global_control='all'
# INNODB options
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 1GB
innodb_change_buffering = all
innodb_change_buffer_max_size = 25
innodb_redo_log_capacity= 250M
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 256
innodb_file_per_table
default_time_zone = +00:00
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-statements-digest=ON
innodb_monitor_enable=all
#skip-log-bin
log_bin = /data/mysql/bin-log
log_bin_index = /data/mysql/bin-log.index
read-only = 0
#binlog_format = STATEMENT
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
gtid_mode = on
enforce_gtid_consistency = on
relay-log=sukai02-relay-bin
[mysqldump]
quick
max_allowed_packet = 50M
[mysql]
no-auto-rehash
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192