Linux 下 MariaDB/MySql 的安装配置、用户管理和备份

1[root@iZ28jaak5nnZ ~]# yum groupinstall mariadb mariadb-client -y

1systemctl start mariadb.service

1[root@iZ28jaak5nnZ ~]# firewall-cmd --add-service=mysql
2success
3[root@iZ28jaak5nnZ ~]# firewall-cmd --list-services
4dhcpv6-client mysql ssh

1[root@iZ28jaak5nnZ ~]# systemctl start mariadb
2[root@iZ28jaak5nnZ ~]# systemctl enable mariadb
3ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/multi-user.target.wants/mariadb.service'

1[root@iZ28jaak5nnZ ~]# mysql_secure_installation
2/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found
3
4NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
5      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
6
7In order to log into MariaDB to secure it, we'll need the current
8password for the root user.  If you've just installed MariaDB, and
9you haven't set the root password yet, the password will be blank,
10so you should just press enter here.
11
12Enter current password for root (enter for none):
13OK, successfully used password, moving on...
14
15Setting the root password ensures that nobody can log into the MariaDB
16root user without the proper authorisation.
17
18Set root password? [Y/n] y
19New password:
20Re-enter new password:
21Password updated successfully!
22Reloading privilege tables..
23 ... Success!
24
25
26By default, a MariaDB installation has an anonymous user, allowing anyone
27to log into MariaDB without having to have a user account created for
28them.  This is intended only for testing, and to make the installation
29go a bit smoother.  You should remove them before moving into a
30production environment.
31
32Remove anonymous users? [Y/n] y
33 ... Success!
34
35Normally, root should only be allowed to connect from 'localhost'.  This
36ensures that someone cannot guess at the root password from the network.
37
38Disallow root login remotely? [Y/n] y
39 ... Success!
40
41By default, MariaDB comes with a database named 'test' that anyone can
42access.  This is also intended only for testing, and should be removed
43before moving into a production environment.
44
45Remove test database and access to it? [Y/n] y
46 - Dropping test database...
47 ... Success!
48 - Removing privileges on test database...
49 ... Success!
50
51Reloading the privilege tables will ensure that all changes made so far
52will take effect immediately.
53
54Reload privilege tables now? [Y/n] y
55 ... Success!

1[root@iZ28jaak5nnZ ~]# mysql -h localhost -u root -p
2Enter password:
3Welcome to the MariaDB monitor.  Commands end with ; or \g.
4Your MariaDB connection id is 12
5Server version: 5.5.50-MariaDB MariaDB Server
6
7Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
8
9Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10
11MariaDB [(none)]>

1创建一个名为shawn的，从localhost登陆的，密码为test的用户
2MariaDB [(none)]> CREATE USER 'shawn'@'localhost' IDENTIFIED BY 'test';
3Query OK, 0 rows affected (0.00 sec)
4
5创建一个名为shawn_test的，任意地点，密码为6666的用户
6MariaDB [(none)]> CREATE USER 'shawn_test'@'%' IDENTIFIED BY '6666';
7Query OK, 0 rows affected (0.00 sec)

1MariaDB [(none)]> DROP USER heiheihei@'localhost';
2Query OK, 0 rows affected (0.00 sec)

1允许从localhost登陆的shawn对mysql数据库的user表执行查询
2MariaDB [(none)]> GRANT SELECT ON mysql.user to shawn@'localhost';
3Query OK, 0 rows affected (0.00 sec)
4
5允许从localhost登陆的shawn对mysql数据库的user表执行查询和插入
6MariaDB [(none)]> GRANT SELECT, INSERT ON mysql.user to shawn@'localhost';
7Query OK, 0 rows affected (0.00 sec)
8
9允许从localhost登陆的shawn对mysql数据库的user表执行增删查改四种操作
10MariaDB [(none)]> GRANT ALL ON mysql.user to shawn@'localhost';
11Query OK, 0 rows affected (0.00 sec)
12
13允许从localhost登陆的shawn对mysql数据库的所有表执行增删查改四种操作
14MariaDB [(none)]> GRANT ALL ON mysql.* to shawn@'localhost';
15Query OK, 0 rows affected (0.00 sec)
16
17允许从localhost登陆的shawn对所有库的所有表执行增删查改四种操作
18MariaDB [(none)]> GRANT ALL ON *.* to shawn@'localhost';
19Query OK, 0 rows affected (0.00 sec)
20
21允许从任意地点登陆的shawn对所有库的所有表执行增删查改四种操作
22MariaDB [(none)]> GRANT ALL ON *.* to shawn@'%';
23Query OK, 0 rows affected (0.00 sec)

1[root@iZ28jaak5nnZ ~]# mysqldump -u root -h localhost -p --all-databases > backup.sql
2Enter password:

1[root@iZ28jaak5nnZ ~]# mysql -u root -h localhost -p < backup.sql
2Enter password: