读书笔记《MySQL性能优化金字塔法则》：1.MySQL初始化安装
目的：
- 为了后续阅读，统一环境、方便演示
前提：
- 准备好一台Linux服务器
环境信息：
| 项 | 值 | 备注 |
| 数据库版本 | 1. MySQL 5.6.x 2. MySQL 5.7.x | 5.6与5.7的版本在安装步骤上有略微的差异，但是基本上都是差不多的 |
| 操作系统版本 | CentOS 6.5 x86-64 | 一样适用的版本：RHEL 7 / OEL 7 |
安装介质：
下载地址：https://downloads.mysql.com/archives/community/
5.7.34：https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
5.6.51：https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz
5.6.35：https://downloads.mysql.com/archives/get/p/23/file/MySQL-5.6.35-1.linux_glibc2.5.x86_64.rpm-bundle.tar
创建Linux操作系统用户：
在Linux上创建用户的时候，先创建用户组，再创建用户。
这里，先创建用户组【mysql】
再创建用户【mysql】，并加入用户组【mysql】
|
1 2 3 |
groupadd mysql useradd mysql -r -g mysql id mysql |
创建程序、数据存放目录：
|
1 2 3 |
mkdir -p /home/mysql/{program,data,conf} mkdir -p /home/mysql/data/mysqldata1/{binlog,innodb_log,innodb_ts,log,mydata,slowlog,sock,tmpdir,undo,relaylog} tree /home/mysql |
解压缩二进制安装文件，并设置目录权限：
将二进制安装文件解压到MySQL的程序目录中：/home/mysql/program
|
1 2 3 |
tar -xzf mysql-5.6.35-linux-glib2.5-x86_64.tar.gz -C /home/mysql/program chown -R mysql.mysql /home/mysql ls -ltr /home/mysql/data/mysqldata1 |
创建软链接，并设置MySQL命令的环境变量：
|
1 2 3 4 5 6 |
ln -s /home/mysql/program/mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql ls -ltr /usr/local/mysql export PATH=$PATH:/usr/local/mysql/bin echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile tail -1 /etc/profile source /etc/profile |
MySQL的配置文件：my.cnf
MySQL数据库的配置文件，在我们的规划中，是存放在：/home/mysql/conf
需要配置好其中与操作系统路径相关的参数
- socket
- pid-file
- datadir
- tmpdir
- log-error
- slow_query_log_file
- log-bin
- relay-log
- innod_data_home_dir
- innodb_log_group_home_dir
- innodb_undo_directory
|
1 2 |
cp -ar /usr/local/mysql/support-files/my-default.cnf /home/mysql/conf/my.cnf ln -s /home/mysql/conf/my.cnf /etc/my.cnf |
文件：my.cnf
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
cat /home/mysql/conf/my.cnf —————————————— [client] # MySQL Socket文件的所在路径 socket=/home/mysql/data/mysqldata1/sock/mysql.sock [mysqld] user=mysql basedir=/usr/local/mysql socket=/home/mysql/data/mysqldata1/sock/mysql.sock pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid datadir=/home/mysql/data/mysqldata1/mydata tmpdir=/home/mysql/data/mysqldata1/tmpdir log-error=/home/mysql/data/mysqldata1/log/err.log slow_query_log slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log log-bin=/home/mysql/data/mysqldata1/binlog/mysql-bin relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin innodb_data_home_dir=/home/mysql/data/mysqldata1/innodb_ts innodb_log_group_home_dir=/home/mysql/data/mysqldata1/innodb_log innodb_undo_directory=/home/mysql/data/mysqldata1/undo |
初始化MySQL数据库：
|
1 2 3 |
cd /usr/local/mysql ./scripts/mysql_install_db --defaults-file=/home/mysql/conf/my.cnf --user=mysql ls -ltr /home/mysql/data/mysqldata1/{mydata,innodb_log,innodb_ts} |
在MySQL 5.7中，取消了脚本：mysql_install_db，因此可以直接使用bin/mysqld进行初始化，它的初始化有两个选项：
- –initialize：使用该选项初始化，会在错误日志中生成一个随机的root口令，可以过滤关键字【A temporary password is generated for root】进行搜索；初始化完成后，启动MySQL服务后进行登录的时候，需要使用该随机密码进行登录，并在登陆后，进行修改
- –initialize-insecure：使用该选项初始化，不会产生随机密码，而是会像5.7之前的版本一样，初始化完成后，第一次登录数据库，root的密码为空
|
1 2 |
mysqld --defaults-file=/etc/my.cnf --initialize mysqld --defaults-file=/etc/my.cnf --initialize-insecure |
启动MySQL服务：
|
1 2 3 4 5 6 7 |
cp -ar /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld chmod +x /etc/init.d/mysqld ls -ltr /etc/init.d/mysqld service mysqld start ps aux | grep mysqld netstat -tupln | grep mysqld cat /home/mysql/data/mysqldata1/log/error.log | grep 'Version' | grep 'socket' | grep 'MySQL' | grep 'Server' |
如果是MS Windows环境，则可以在Command终端窗口中执行下面的命令：
|
1 |
netstat -ano | findstr '3306' |
MySQL的服务成功启动后，会在错误日志中打印一条有关MySQL版本号与socket文件的信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
2021-08-31 9:07:07 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2021-08-31 9:07:07 0 [Note] InnoDB: Uses event mutexes 2021-08-31 9:07:07 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 2021-08-31 9:07:07 0 [Note] InnoDB: Number of pools: 1 2021-08-31 9:07:07 0 [Note] InnoDB: Using SSE2 crc32 instructions 2021-08-31 9:07:07 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M 2021-08-31 9:07:07 0 [Note] InnoDB: Completed initialization of buffer pool 2021-08-31 9:07:07 0 [Note] InnoDB: 128 out of 128 rollback segments are active. 2021-08-31 9:07:07 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2021-08-31 9:07:07 0 [Note] InnoDB: Setting file 'D:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2021-08-31 9:07:07 0 [Note] InnoDB: File 'D:\xampp\mysql\data\ibtmp1' size is now 12 MB. 2021-08-31 9:07:07 0 [Note] InnoDB: Waiting for purge to start 2021-08-31 9:07:07 0 [Note] InnoDB: 10.4.20 started; log sequence number 300315; transaction id 170 2021-08-31 9:07:07 0 [Note] InnoDB: Loading buffer pool(s) from D:\xampp\mysql\data\ib_buffer_pool 2021-08-31 9:07:07 0 [Note] Plugin 'FEEDBACK' is disabled. 2021-08-31 9:07:07 0 [Note] Server socket created on IP: '::'. |
在以上的步骤中，就完成了一个初步的MySQL的安装部署；
接下来对MySQL做简单的【安全加固】；
登录MySQL后，查看当前登录的用户：
|
1 |
select user(); |
查看MySQL的版本：
|
1 |
select version(); |
删除非root用户或者非localhost用户，并修改root的密码：
在默认情况下MySQL初始化完成后，会创建一些默认用户：
- 匿名用户
- 允许127.0.0.1和localhost登录的非root用户
建议删除这些无用且可能给数据库带来风险的用户。
查看当前数据库的用户：
|
1 2 3 |
select user,host from mysql.user; delete from mysql.user where user!='root' or host!='localhost'; delete from mysql.user where user not in ('mysql.sys','mysql.session','mysqlxsys','root','mysql.infoschema') or host not in ('localhost'); |
设置root密码：
|
1 2 3 4 5 6 7 8 9 |
-- MySQL 5.7.X 之前的版本 set password for 'root'@'localhost' = password('new_password'); flush privileges; -- MySQL 5.7.X 直接使用明文的方式将密码更新到表【mysql.user】中，密码会被自动转为加密格式 -- MySQL 8.X 不能使用直接更新表【mysql.user】的方式 |
清理test库：
在默认情况下，MySQL 5.6.X初始化安装之后，会生成一个测试用途的库：test
这个库在生产环境中一般是不需要的，如果确定不使用，建议删除。
清理数据表：mysql.db：
在默认情况下，MySQL 5.6.X初始化安装完成后，在mysql.db中会有针对test库的任意用户、任意地址的访问权限；
无任何权限用户或者匿名用户登录MySQL后，都可以对test库进行任意操作；
因此，建议MySQL初始化安装完成后，清理这些不安全的用户，或者删除mysql.db表中对test库预设的访问权限；
|
1 2 3 4 5 6 |
show databases; drop database test; select * from mysql.db\G truncate mysql.db; delete from mysql.db where user not in ('mysql.sys','mysql.session','mysqlxsys','root','mysql.infoschema') or host not in ('localhost'); flush privileges; |
创建：用户、库、表、数据
创建用户的时候不要使用DML直接操作mysql.user，而是要使用命令：
- grant
- revoke
- create user
- drop user
在MySQL 5.7中，原password字段，变更为了【authentication_string】
|
1 2 3 4 5 6 7 |
-- 创建用户 create user user_name@'domain_string' identified by 'password_string'; grant all on *.* to user_name@'domain_string' identified by 'password_string'; grant all on *.* to user_name@'domain_string' with grant option; show grants show grants for user; show grants for user@domain; |
创建库、表、程序账号：
创建程序账号，在生产环境中不建议直接使用root账号，最好是新建一个管理员账号与程序账号
程序账号一般给开发者使用，权限推荐：
- create routine
- alter routine
- execute
- select
- delete
- insert
- update
程序账号需要：
- 指定具体的库或表
- 并且，指定具体的访问来源
遵循最小化原则；
|
1 2 3 4 5 6 7 8 |
-- 与应用相关 create database [app_name]_db; use [app_name]_db; create user program@'ip_addr' identified by 'xxx'; grant create routine,alter routine,execute,select,delete,update,insert on [app_name]_db.* to program@'ip_addr'; flush privileges; |
创建完应用相关的资源、账号，并分配权限后，需要验证权限是否配置正确：
用分配的账号访问应用的库，并执行基本的建表、插入数据等操作，验证无误后，就可以正式交付给应用方使用，交付的信息格式：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
———————————— 环境：XXX 应用：XXX ———————————— 数据库厂商：MySQL 数据库版本：5.6.35 连接信息： IP：xxx.xxx.xxx.xxx PORT: 3306 Schema_name / DB_name: xxxx_db Username: xxxx_admin Domain: ip_addr / xxx.xxx.xxx.% / % / localhost Password: xxxx ———————————— 权限：select, update, delete, insert ———————————— 操作系统： 发行版：CentOS 7.X 防火墙：已关 / 已放行 SELinux：已关 / 已放行 ———————————— 交付人：Leviathan 所属部门：DB Force 交付时间：2021-8-31 10:23:26 ———————————— |
MySQL参数文件模板
MySQL 5.7.X：my.cnf
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 |
[client] loose_default-character-set = utf8 port = 3306 socket = /home/mysql/data/mysqldata1/sock/mysql.sock [mysqldump] quick max_allowed_packet = 2G default-character-set = utf8 [mysql] no-auto-rehash show-warnings prompt="\\u@\\h : \\d \\r:\\m:\\s> " default-character-set = utf8 [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] user=mysql open-files-limit = 65536 [mysqld] #large-pages # ============================= Common Parameters default-storage-engine = INNODB character-set-server = utf8 collation-server = uts8_bin log_timestamps = SYSTEM user = mysql port = 3306 socket = /home/mysql/data/mysqldata1/sock/mysql.sock pid-file = /home/mysql/data/mysqldata1/sock/mysql.pid datadir = /home/mysql/data/mysqldata1/mydata tmpdir = /home/mysql/data/mysqldata1/tmpdir skip-name-reslove skip_external_locking lower_case_table_name = 1 event_scheduler = 0 back_log = 512 default-time-zone = '+8:00' max_connections = 3000 max_connect_errors = 99999 max_allowed_packet = 64M max_heap_table_size = 8M max_length_for_sort_data = 16k slave_pending_jobs_size_max = 128M wait_timeout = 172800 interactive_timeout = 172800 net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 2M sort_buffer_size = 2M join_buffer_size = 4M binlog_cache_size = 2M table_open_cache = 4096 table_open_cache_instances = 2 table_definition_cache = 4096 thread_cache_size = 512 tmp_table_size = 8M # QC[查询缓存]系统变量，在MySQL 8.0.3中被移除 query_cache_size=0 query_cache_type=OFF # ============================= Log Related Settings log-error=/home/mysql/data/mysqldata1/log/error.log long_query_time=1 slow_query_log slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log log_slow_slave_statements #log_queries_not_using_indexes # ============================= Replication Related Settings # ---------------- # For Master server-id = 330614 log-bin = /home/mysql/data/mysqldata1/binlog/mysql-bin binlog-format = ROW binlog-checksum = CRC32 binlog-rows-query-log-events = 1 binlog_max_flush_queue_time = 1000 max_binlog_size = 512M expire_logs_days = 14 sync_binlog = 1 master-verify-checksum = 1 master-info-repository = TABLE auto_increment_increment = 2 auto_increment_offset = 2 # 开启多线程复制后，如果从库意外挂掉 # 使用【relay_log_recovery=1】，crash recovery时会到relay log中查找用于补齐gaps的日志 # 如果relay log没有实时落盘，则会导致复制启动可能报错：ERROR 1872(HY000): Slave failed to initialize relay log info structure from the repository # 可以使用【sync_relay_log=1】来尽量避免 # 如果不使用【sync_relay_log=1】 # 则报错的时候要使用【stop slave; change master to master_auto_position=1; start slave;】让从库清理掉relay log，并重新到主库中找位置 #sync_relay_log = 1 # ---------------- # For Slave relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin relay-log-info-repository=TABLE relay-log-recovery=1 #slave-skip-errors=1022,1032,1062,1236 slave-parallel-workers = 4 slave-sql-verify-checksum = 1 slave-net-timeout = 10 log_bin_trust_function_creators = 1 log_slave_updates = 1 # ============================= MyISAM specific options key_buffer_size = 8M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 64M myisam_max_sort_file_size = 10G myisam_repaire_threads = 1 myisam_recover_options = force # ============================= InnoDB specific options # ################## Data Options innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts innodb_data_file_path = ibdata1:2048M:autoextend innodb_file_per_table innodb_strict_mode = 1 innodb_flush_method = O_DIRECT innodb_checksum_algorithm = crc32 innodb_autoinc_lock_mode = 2 # 以下三个format系统变量在MySQL 8.0中被移除，默认使用：barracuda innodb_file_format = barracuda innodb_file_format_max = barracuda innodb_file_format_check = ON # ################## Buffer Pool Options innodb_buffer_pool_size = 6G innodb_buffer_pool_instances = 4 innodb_buffer_pool_dump_at_shutdown = ON innodb_buffer_pool_load_at_startup = ON innodb_adaptive_flushing = ON innodb_change_buffering = all innodb_flush_neighbors = 0 innodb_old_blocks_time = 1000 innodb_lru_scan_depth = 4096 innodb_max_dirty_pages_pct = 75 # MySQL 8.0中被废弃 #innodb_adaptive_hash_index_partitions = 32 # ################## Redo Options innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log innodb_log_buffer_size = 128M innodb_log_file_size = 2G innodb_log_files_in_group = 2 innodb_flush_log_at_trx_commit = 1 innodb_fast_shutdown = 1 # MySQL 8.0中被废弃，内部默认开启XA innodb_support_xa = ON # ################## Transaction Options innodb_tread_concurrency = 64 innodb_lock_wait_timeout = 120 innodb_rollback_on_timeout = 1 transaction_isolation = READ-COMMITTED # ################## IO Options performance_schema = on innodb_use_native_aio = 1 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 20000 # ################## Undo Options innodb_undo_directory = /home/mysql/data/mysqldata1/undo/ innodb_undo_tablespaces = 4 innodb_undo_log_truncate = ON innodb_purge_threads = 4 innodb_purge_batch_size = 512 innodb_max_purge_lag = 65536 # ################## Options # ################## Options # ################## Options # ################## Options # ============================= MySQL 5.6 # ################## GTID gtid-mode = on enforce-gtid-consistency = true optimizer_switch='mrr=on, mrr_cost_based=off, batched_key_access=on' # ============================= MySQL 5.7 #super_read_only = on explicit_defaults_for_timestamp = ON secure_file_priv = null innodb_page_cleaners = 4 slave_parallel_type = LOGICAL_CLOCK slave_rows_search_algorithms = 'INDEX_SCAN, HASH_SCAN' |
