Shulou(Shulou.com)06/01 Report--

1. View the version of the database:

MariaDB [(none)] > select version (); +-+ | version () | +-+ | 5.5.52-MariaDB | +-+ 1 row in set (0.18 sec)

2. Delete redundant accounts (except for root and localhost)

MariaDB [(none)] > use mysqlMariaDB [mysql] > select user,host from mysql.user +-+-+ | user | host | +-+ | root | 127.0.0.1 | | root |:: 1 | | localhost | | Root | localhost | | lvs-dr01.saltstack.com | | root | lvs-dr01.saltstack.com | +-+-+ 6 rows in set (0.03 sec) MariaDB [mysql] > delete from mysql.user where (user) Host) not in (select 'root','localhost') Query OK, 5 rows affected (0.05sec) MariaDB [mysql] > select user,host from mysql.user;+-+-+ | user | host | +-+-+ | root | localhost | +-+-+ 1 row in set (0.05 sec)

3. Modify the default mysql management account of mysql

Modify the default mysql management account (change root to mysql and set the new password to redhat12345)

MariaDB [mysql] > update user set user= "admin" where user= "root"; MariaDB [mysql] > update mysql.user set user='admin',password=password ('redhat12345'); Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0MariaDB [mysql] > flush privileges;Query OK, 0 rows affected (0.03 sec) [root@LVS-DR01 ~] # mysql-uadmin-p'redhat12345'MariaDB [(none)] > user mysql;MariaDB [mysql] > show tables

Continue to inquire:

MariaDB [mysql] > select user,host from mysql.user;+-+-+ | user | host | +-+-+ | admin | localhost | +-+-+ 1 row in set (0.00 sec)

4. Delete test database:

MariaDB [mysql] > drop database test;MariaDB [mysql] > flush privileges;MariaDB [(none)] > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | +-+ 3 rows in set (0.06 sec)

5. Optimize the permission dictionary table mysql.db

As follows: after creating a new MySQL database, the test database permissions created by default are weird, and all connected users can access the database and manipulate the objects in it. Host is% and User is empty, indicating that there are no restrictions. All users who can connect to MySQL have almost all the permissions of the database at the beginning of test and test.

MariaDB [mysql] > select * from mysql.db where db like 'test%'\ gateway * 1. Row * * Host:% Db: test User: Select_priv : Y Insert_priv: Y Update_priv: Y Delete_priv: YCreate_ priv: Y Drop_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: YCreate_tmp_table_priv: Y Lock_tables_priv: YCreate_ view_priv: Y Show_view_priv: Y Create_routine_priv: y Alter_routine_priv: n Execute_priv: n Event_priv: y Trigger_priv: y row * * Host:% Db: test\ _% User: Select_priv: y Insert_priv: y Update_priv: y Delete_priv: YCreate_ priv: y Drop_priv: y Grant_priv: n References_priv: y Index_priv: y Alter_priv: YCreate_tmp_table_priv: y Lock_tables_priv: y Create_view_priv: y Show_view_priv: y Create_routine_priv: y Alter_routine_priv: n Execute_priv: n Event_priv: y Trigger_priv: Y2 rows in set (0.31 sec) optimized operation: MariaDB [mysql] > truncate table mysql.db Query OK, 0 rows affected (0.01 sec) MariaDB [mysql] > select * from mysql.db where db like 'test%'\ GEmpty set (0.00 sec)

6. How to optimize / root/.mysql_history file

[root@LVS-DR01] # tail-20 ~ / .mysql_history flush privileges;select user,host from mysql.user;delete from mysql.user where user= "'molewan1'@'10.10.10.%'"; delete from mysql.db where user='molewan1'@'10.10.10.%';select user,host from mysql.user;delete from mysql.user where user=molewan1;delete from mysql.user where user='molewan1@10.10.10.%';flush privileges;select user,host from mysql.user Delete from mysql.user where user= "molewan1" and host= "10.10.10.%"; flush privileges;select user,host from mysql.user;delete from mysql.user where user= "molewan" and host= "10.10.10.%"; flush privileges;create user molewan@'10.10.10.%' identified by 'molewan';select user,host from mysql.user;desc mysql.user;update mysql.user set password=password (' admin') where user='admin' and host='localhost';flush privileges;\ Q

Note: under the Linux/Unix system, all operations performed using the mysql command line tool will be recorded in a file called .mysql _ history, which is saved by default in the root directory of the current user.

This setting was originally designed to improve the mysql command line experience. Commands can be flipped up and down in mysql, but in some cases it can cause hidden dangers.

How to eliminate hidden dangers:

Method 1: the operation based on the DB layer modifies the MYSQL_HISTFILE environment variable and changes its value to / dev/null, so that all operations will be output to null, and the history of the operation will not be preserved. Method 2: keep the file based on the system-level operation, but change the soft link of the file that is actually not / dev/null, so that all operations will be output to empty, and the history of the operation will not be preserved naturally. Ln-f-s / dev/null ~ / .mysql_ output [root @ LVS-DR01 ~] # tail-20 ~ / .mysql_history has no output at this time

At this point, the basic optimization after the installation has been completed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.