第1章 MySQL初始化安装、简单安全加固 - xiaoboluo768/qianjinliangfang GitHub Wiki
1.2.1
[root@localhost ~]# cd /root
[root@localhost ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.6/mysql- 5.6.35-linux-glibc2.5-\
x86_64.tar.gz
[root@localhost ~]# ll mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz
-rw-r--r-- 1 root root 314581668 2月 12 23:04 mysql-5.6.35-linux- glibc2.5-x86_64.tar.gz
1.2.2
[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd mysql -r -g mysql
# 验证用户组和用户
[root@localhost ~]# id mysql
uid=500(mysql) gid=500(mysql) 组=500(mysql)
1.2.3
[root@localhost ~]# mkdir /home/mysql/{program,data,conf} -p
[root@localhost ~]# mkdir /home/mysql/data/mysqldata1/{mydata,sock,tmpdir,log,innodb_ts,\
innodb_log,undo,slowlog,binlog,relaylog} -p
# 查看创建目录结果
[root@localhost ~]# tree /home/mysql/ #如果没有这个命令，就使用yum install tree -y 安装
......
13 directories, 0 files
1.2.4
[root@localhost ~]# cd /root
[root@localhost ~]# tar xf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz -C/home/mysql/\
program/
[root@localhost ~]# chown mysql.mysql /home/mysql -R
# 查看datadir关键目录的权限是否正确
[root@localhost ~]# ll /home/mysql/data/mysqldata1/
......
1.2.5
[root@localhost ~]# ln –s\
/home/mysql/program/mysql-5.6.35-linux-glibc2.5-x86_64 /usr/ local/mysql
# 查看basedir关键程序目录是否可用
[root@localhost ~]# ll /usr/local/mysql/
......
[root@localhost ~]# export PATH=$PATH:/usr/local/mysql/bin/
[root@localhost ~]# echo 'export PATH=$PATH:/usr/local/mysql/bin/' >> /etc/profile
# 查看环境变量配置是否成功添加到/etc/profile文件中
[root@localhost ~]# tail -1 /etc/profile
export PATH=$PATH:/usr/local/mysql/bin/
1.2.6
[root@localhost ~]# cp -ar /usr/local/mysql/support-files/my-default.cnf /home/mysql/\
conf/my.cnf
[root@localhost ~]# ln -s /home/mysql/conf/my.cnf /etc/my.cnf
# my.cnf配置文件内容如下
[root@localhost ~]# cat /home/mysql/conf/my.cnf
[client]
socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径
[mysqld]
user=mysql
basedir = /usr/local/mysql
socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径
pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid # pid文件所在路径
datadir=/home/mysql/data/mysqldata1/mydata # 数据文件路径
tmpdir=/home/mysql/data/mysqldata1/tmpdir # 存放临时文件的路径
log-error=/home/mysql/data/mysqldata1/log/error.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/
1.2.7
[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# ./scripts/mysql_install_db --defaults-file=/home/ mysql/conf/my.\
cnf--user=mysql
......
# 查看关键目录在初始化之后是否有正确的数据文件和目录、权限
[root@localhost mysql]# ll /home/mysql/data/mysqldata1/{mydata,innodb_log, innodb_ts}/
......
：
[root@localhost mysql]# mysqld --defaults-file=/etc/my.cnf --initialize
[root@localhost mysql]# mysqld --defaults-file=/etc/my.cnf --initialize-insecure
1.2.8
[root@localhost mysql]# cp -ar /usr/local/mysql/support-files/mysql.server /etc/init.d/\
mysqld
[root@localhost mysql]# chmod +x /etc/init.d/mysqld
# 查看/etc/init.d/mysqld是否被成功赋予执行权限
[root@localhost mysql]# ll /etc/init.d/mysqld
-rwxr-xr-x 1 mysql mysql 10875 11月 28 23:32 /etc/init.d/mysqld
[root@localhost mysql]# service mysqld start
Starting MySQL.. [确定]
# 查看进程和端口
[root@localhost mysql]# ps aux |grep mysqld
......
[root@localhost mysql]# netstat -ntupl |grep mysqld
tcp 0 0 :::3306 :::* LISTEN 10743/mysqld
# 查看错误日志
[root@localhost mysql]# vim /home/mysql/data/mysqldata1/log/error.log
# 注意：日志中不能出现ERROR错误，看到最后一行输出版本号和socket信息就表示MySQL启动成功
Version: '5.6.35-log' socket: '/home/mysql/data/mysqldata1/sock/mysql. sock' port: 3306 MySQL Community Server (GPL)
1.3.1
[root@localhost mysql]# mysql
......
# 查看当前登录用户
mysql> select user();
......
1 row in set (0.00 sec)
# 查看当前MySQL版本是否正确
mysql> select version();
......
1 row in set (0.00 sec)
1.3.2
mysql> select user,host from mysql.user;
......
6 rows in set (0.00 sec)
mysql> delete from mysql.user where user!='root' or host!='localhost';
Query OK, 5 rows affected (0.01 sec)
## 如果是MySQL 5.7.x 较新的版本或者8.0.x版本，则删除操作需要排除几个系统用户
mysql> DELETE FROM mysql.user WHERE user NOT IN ('mysql.sys', 'mysql.session','mysqlxsys', 'root','mysql.infoschema') OR host NOT IN ('localhost');
# 查看删除结果是否正确
mysql> select user,host from mysql.user;
......
1 row in set (0.00 sec)
mysql> set password for 'root'@'localhost' = PASSWORD('admin'); # 在MySQL 5.7.x版本中可以不需要PASSWORD函数，直接使用明文密码也可以自动转换为加密格式密码写入mysql.user表中，且该用法将在后续版本中移除
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
# 重新使用新密码登录MySQL
[root@localhost mysql]# mysql -uroot -p
......
1.3.3
mysql> show databases;
......
4 rows in set (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
# 查看删除结果是否正确
mysql> show databases;
......
3 rows in set (0.00 sec)
mysql> select * from mysql.db\G # MySQL 5.7.x版本移除了test库之后，该库的权限也没有了，但增加了sys库，有对应的sys库的默认权限，所以5.7.x版本忽略清理该表
......
mysql> truncate mysql.db;
Query OK, 0 rows affected (0.00 sec)
## 如果是MySQL 5.7.x 较新的版本或者8.0.x版本，则清理操作需要排除几个系统用户
mysql> DELETE FROM mysql.db where user NOT IN ('mysql.sys', 'mysql.session','mysqlxsys', 'root','mysql.infoschema') OR host NOT IN ('localhost') ;
# 查看清理结果是否正确
mysql> select * from mysql.db\G
Empty set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
1.4.1
# 创建管理用户
mysql> create user 'gangshen'@'%' identified by 'admin';
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'gangshen'@'localhost' identified by 'admin';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'gangshen'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
# 注：在MySQL 5.7.x较新的版本及其8.0.x版本中，在授予%号地址来源时也同时包含了localhost，不再单独区分
mysql> grant all on *.* to 'gangshen'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)
# 使用新创建的管理账号重新登录MySQL，验证这个管理账号是否可用
[root@localhost mysql]# mysql -ugangshen -p
......
mysql> show grants;
......
1 row in set (0.00 sec)
1.4.2
# 使用管理员账号创建库、表
mysql> create database shengang_db;
Query OK, 1 row affected (0.00 sec)
mysql> use shengang_db
Database changed
mysql> create table shengang_table(id int primary key auto_increment, shengang_test varchar(50),datetime_current datetime);
Query OK, 0 rows affected (0.02 sec)
# 创建程序账号并赋予权限
mysql> create user 'program'@'192.168.2.105' identified by 'admin';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'program'@'localhost' identified by 'admin';
Query OK, 0 rows affected (0.00 sec)
mysql> grant create routine,alter routine,execute,select,delete, insert,update on shengang_db.* to 'program'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> grant create routine,alter routine,execute,select,delete,insert, update on shengang_db.* to 'program'@'192.168.2.105';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
1.4.3
[root@localhost mysql]# mysql -uprogram -p
......
mysql> select user();
......
1 row in set (0.00 sec)
# 查看程序账号的权限是否正确
mysql> show grants;
......
2 rows in set (0.00 sec)
mysql> show databases;
......
2 rows in set (0.00 sec)
mysql> use shengang_db
......
mysql> show tables;
......
1 row in set (0.00 sec)
# 查看表结构是否创建正确
mysql> show create table shengang_table;
......
1 row in set (0.00 sec)
mysql> insert into shengang_table(`shengang_test`,`datetime_current`) values('shengang', now());
Query OK, 1 row affected (0.00 sec)
# 查看插入数据是否正确
mysql> select * from shengang_table;
......
1 row in set (0.00 sec)
mysql>
1.5
[root@localhost ~]# cat /home/mysql/conf/my1.cnf
[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 = 65535
[mysqld]
#large-pages
#*************************common parameters************************
default-storage-engine = INNODB
character-set-server=utf8
collation_server = utf8_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-resolve
skip_external_locking
lower_case_table_names=1
event_scheduler=0
back_log=512
default-time-zone='+8:00'
max_connections = 3000
max_connect_errors=99999
max_allowed_packet = 64M
slave_pending_jobs_size_max=128M
max_heap_table_size = 8M
max_length_for_sort_data = 16k
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
#*********************** Logs 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=15
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
log_bin_trust_function_creators=1
log_slave_updates=1
slave-net-timeout=10
#********************** MyISAM Specific options *********************
key_buffer_size = 8M
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_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
# 以下三个format系统变量在MySQL 8.0中被移除，内部默认使用barracuda
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_file_format_check = ON
innodb_strict_mode = 1
innodb_flush_method = O_DIRECT
innodb_checksum_algorithm=crc32
innodb_autoinc_lock_mode=2
#### Buffer Pool options
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 4
innodb_max_dirty_pages_pct = 75
innodb_adaptive_flushing = ON
innodb_flush_neighbors = 0
innodb_lru_scan_depth = 4096
innodb_change_buffering = all
innodb_old_blocks_time = 1000
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON
# 在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_thread_concurrency = 64
innodb_lock_wait_timeout = 120
innodb_rollback_on_timeout = 1
transaction_isolation = READ-COMMITTED
#### IO options
performance_schema=on
innodb_read_io_threads = 8
innodb_write_io_threads = 16
innodb_io_capacity = 20000
innodb_use_native_aio = 1
#### 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
#### MySQL 5.6
#### GTID
gtid-mode=on # GTID only
enforce-gtid-consistency=true # GTID only
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
slave_parallel_type=LOGICAL_CLOCK
slave_rows_search_algorithms='INDEX_SCAN,HASH_SCAN'
innodb_page_cleaners=4
- 温馨提示：关于文中提到的参数的详细解释，可参考本书下载资源中的“附录C”。
上一篇：书中代码段以及高清大图目录 | 下一篇：第2章 MySQL常用的两种升级方法