专职DBA-MySQL5.6初始化安装 周万春 1.下载安装二进制安装文件 db01 [~] 2020-06-30 21:46:17 root@pts/0 # cd /disk/ db01 [/disk] 2020-06-30 21:48:27 root@pts/0 # wget -c https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz 2.创建mysql用户 db01 [/disk] 2020-06-30 21:52:47 root@pts/0 # groupadd mysql db01 [/disk] 2020-06-30 21:52:53 root@pts/0 # useradd mysql -r -g mysql db01 [/disk] 2020-06-30 21:53:04 root@pts/0 # id mysql uid=1000(mysql) gid=1000(mysql) groups=1000(mysql) 3.创建程序、数据存放目录 db01 [/disk] 2020-06-30 21:54:20 root@pts/0 # mkdir /home/mysql/{program,data,conf} -p db01 [/disk] 2020-06-30 21:54:58 root@pts/0 # mkdir /home/mysql/data/mysqldata1/{mydata,sock,tmpdir,log,innodb_ts,innodb_log,undo,slowlog,binlog,relaylog} -p db01 [/disk] 2020-06-30 21:56:14 root@pts/0 # tree /home/mysql/ /home/mysql/ |-- conf |-- data | `-- mysqldata1 | |-- binlog | |-- innodb_log | |-- innodb_ts | |-- log | |-- mydata | |-- relaylog | |-- slowlog | |-- sock | |-- tmpdir | `-- undo `-- program 14 directories, 0 files 4.解压缩二进制安装文件并设置目录权限 db01 [/disk] 2020-06-30 21:57:39 root@pts/0 # tar -xf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz -C /home/mysql/program/ db01 [/disk] 2020-06-30 21:58:18 root@pts/0 # chown mysql:mysql /home/mysql -R db01 [/disk] 2020-06-30 21:58:34 root@pts/0 # ls -l /home/mysql/data/mysqldata1/ total 40 drwxr-xr-x 2 mysql mysql 4096 Jun 30 21:56 binlog drwxr-xr-x 2 mysql mysql 4096 Jun 30 21:56 innodb_log drwxr-xr-x 2 mysql mysql 4096 Jun 30 21:56 innodb_ts drwxr-xr-x 2 mysql mysql 4096 Jun 30 21:56 log drwxr-xr-x 2 mysql mysql 4096 Jun 30 21:56 mydata drwxr-xr-x 2 mysql mysql 4096 Jun 30 21:56 relaylog drwxr-xr-x 2 mysql mysql 4096 Jun 30 21:56 slowlog drwxr-xr-x 2 mysql mysql 4096 Jun 30 21:56 sock drwxr-xr-x 2 mysql mysql 4096 Jun 30 21:56 tmpdir drwxr-xr-x 2 mysql mysql 4096 Jun 30 21:56 undo 5.软链接程序路径，并设置MySQL命令环境变量 db01 [~] 2020-06-30 21:59:44 root@pts/0 # ln -s /home/mysql/program/mysql-5.6.35-linux-glibc2.5-x86_64/ /usr/local/mysql db01 [~] 2020-06-30 22:02:31 root@pts/0 # echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile db01 [~] 2020-06-30 22:06:17 root@pts/0 # source /etc/profile 6.配置my.cnf文件参数 db01 [~] 2020-06-30 22:06:23 root@pts/0 # cp -a /usr/local/mysql/support-files/my-default.cnf /home/mysql/conf/my.cnf db01 [~] 2020-06-30 22:09:41 root@pts/0 # ln -s /home/mysql/conf/my.cnf /etc/my.cnf db01 [~] 2020-06-30 22:10:42 root@pts/0 # cat /etc/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/ 7.初始化MySQL db01 [~] 2020-06-30 22:11:28 root@pts/0 # cd /usr/local/mysql/ db01 [/usr/local/mysql] 2020-06-30 22:11:59 root@pts/0 # ./scripts/mysql_install_db --defaults-file=/home/mysql/conf/my.cnf --user=mysql 查看关键目录在初始化之后是否有正确的数据文件和目录、权限 db01 [/usr/local/mysql] 2020-06-30 22:14:08 root@pts/0 # ll /home/mysql/data/mysqldata1/{mydata,innodb_log,innodb_ts}/ /home/mysql/data/mysqldata1/innodb_log/: total 98304 -rw-rw---- 1 mysql mysql 50331648 Jun 30 22:13 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Jun 30 22:13 ib_logfile1 /home/mysql/data/mysqldata1/innodb_ts/: total 12288 -rw-rw---- 1 mysql mysql 12582912 Jun 30 22:13 ibdata1 /home/mysql/data/mysqldata1/mydata/: total 12 drwx------ 2 mysql mysql 4096 Jun 30 22:13 mysql drwx------ 2 mysql mysql 4096 Jun 30 22:13 performance_schema drwx------ 2 mysql mysql 4096 Jun 30 22:13 test 8.启动MySQL db01 [~] 2020-06-30 22:15:41 root@pts/0 # cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld db01 [~] 2020-06-30 22:16:01 root@pts/0 # chmod +x /etc/init.d/mysqld db01 [~] 2020-06-30 22:16:31 root@pts/0 # ls -l /etc/init.d/mysqld -rwxr-xr-x 1 mysql mysql 10875 Nov 28 2016 /etc/init.d/mysqld db01 [~] 2020-06-30 22:16:56 root@pts/0 # /etc/init.d/mysqld start Starting MySQL. [ OK ] db01 [~] 2020-06-30 22:17:07 root@pts/0 # ps -ef | grep mysqld root 3857 1 0 22:17 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/home/mysql/data/mysqldata1/mydata --pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid mysql 4125 3857 2 22:17 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/home/mysql/data/mysqldata1/mydata --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/home/mysql/data/mysqldata1/log/error.log --pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid --socket=/home/mysql/data/mysqldata1/sock/mysql.sock root 4154 3548 0 22:17 pts/0 00:00:00 grep --color=auto mysqld db01 [~] 2020-06-30 22:17:21 root@pts/0 # netstat -lnp | grep mysqld tcp6 0 0 :::3306 :::* LISTEN 4125/mysqld unix 2 [ ACC ] STREAM LISTENING 10946035 4125/mysqld /home/mysql/data/mysqldata1/sock/mysql.sock db01 [~] 2020-06-30 22:17:42 root@pts/0 # cat /home/mysql/data/mysqldata1/log/error.log ... Version: ‘5.6.35-log‘ socket: ‘/home/mysql/data/mysqldata1/sock/mysql.sock‘ port: 3306 MySQL Community Server (GPL) 简单安全加固 9.登录MySQL db01 [~] 2020-06-30 22:20:54 root@pts/0 # mysql mysql> select user(),current_user(); +----------------+----------------+ | user() | current_user() | +----------------+----------------+ | root@localhost | root@localhost | +----------------+----------------+ 1 row in set (0.00 sec) mysql> select version(); +------------+ | version() | +------------+ | 5.6.35-log | +------------+ 1 row in set (0.00 sec) 10.删除非root或非localhost的用户并修改root密码 mysql> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | | db01 | | root | db01 | | | localhost | | root | localhost | +------+-----------+ 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> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | localhost | +------+-----------+ 1 row in set (0.00 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> set password for ‘root‘@‘localhost‘ = PASSWORD(‘123‘); Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> \q Bye db01 [~] 2020-06-30 22:26:23 root@pts/0 # mysql -uroot -p 11.删除test库，清理mysql.db表 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> drop database test; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) mysql> select * from mysql.db\G Empty set (0.00 sec) 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> flush privileges; Query OK, 0 rows affected (0.00 sec) 12.创建用户并授权 mysql> create user ‘dba‘@‘localhost‘ identified by ‘123‘; Query OK, 0 rows affected (0.00 sec) mysql> create user ‘dba‘@‘%‘ identified by ‘123‘; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to ‘dba‘@‘localhost‘ with grant option; Query OK, 0 rows affected (0.01 sec) mysql> grant all on *.* to ‘dba‘@‘%‘ with grant option; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) db01 [~] 2020-06-30 22:33:00 root@pts/0 # mysql -udba -p mysql> select user(),current_user(); +---------------+----------------+ | user() | current_user() | +---------------+----------------+ | dba@localhost | dba@localhost | +---------------+----------------+ 1 row in set (0.00 sec) mysql> show grants; +---------------------------------------------------------------------------------------------------------------------------------------+ | Grants for dba@localhost | +---------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO ‘dba‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257‘ WITH GRANT OPTION | +---------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 13.创建库、表、程序账号 mysql> create database app01; Query OK, 1 row affected (0.00 sec) mysql> use app01; Database changed mysql> CREATE TABLE `t1` ( -> `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增ID‘, -> `c1` varchar(64) NOT NULL DEFAULT ‘UUID()‘ COMMENT ‘随机UUID号‘, -> `c2` varchar(64) NOT NULL DEFAULT ‘@@hostname‘ COMMENT ‘系统主机名‘, -> `c3` varchar(10) NOT NULL DEFAULT ‘@@port‘ COMMENT ‘数据库实例端口‘, -> `intime` datetime NOT NULL DEFAULT NOW() COMMENT ‘数据插入时间‘, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT ‘测试表‘; Query OK, 0 rows affected (0.02 sec) mysql> create user ‘dev‘@‘%‘ identified by ‘123‘; Query OK, 0 rows affected (0.00 sec) mysql> grant create routine,alter routine,execute,select,delete,insert,update on app01.* to ‘dev‘@‘%‘; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 14.插入数据 db01 [~] 2020-06-30 22:38:23 root@pts/0 # mysql -udev -p mysql> select user(),current_user(); +---------------+----------------+ | user() | current_user() | +---------------+----------------+ | dev@localhost | dev@% | +---------------+----------------+ 1 row in set (0.00 sec) mysql> show grants; +--------------------------------------------------------------------------------------------------------+ | Grants for dev@% | +--------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO ‘dev‘@‘%‘ IDENTIFIED BY PASSWORD <secret> | | GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `app01`.* TO ‘dev‘@‘%‘ | +--------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | app01 | +--------------------+ 2 rows in set (0.00 sec) mysql> use app01; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-----------------+ | Tables_in_app01 | +-----------------+ | t1 | +-----------------+ 1 row in set (0.00 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增ID‘, `c1` varchar(64) NOT NULL DEFAULT ‘UUID()‘ COMMENT ‘随机UUID号‘, `c2` varchar(64) NOT NULL DEFAULT ‘@@hostname‘ COMMENT ‘系统主机名‘, `c3` varchar(10) NOT NULL DEFAULT ‘@@port‘ COMMENT ‘数据库实例端口‘, `intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘数据插入时间‘, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=‘测试表‘ 1 row in set (0.00 sec) mysql> insert into t1(c1,c2,c3) values(uuid(),@@hostname,@@port); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> select * from t1; +----+--------------------------------------+------+------+---------------------+ | id | c1 | c2 | c3 | intime | +----+--------------------------------------+------+------+---------------------+ | 1 | d644041c-badf-11ea-aaad-00163e1693be | db01 | 3306 | 2020-06-30 22:41:52 | +----+--------------------------------------+------+------+---------------------+ 1 row in set (0.00 sec)