MySQL Management
Start-up and shut down the MySQL server
First, we need the following command to check whether the MySQL server is started:
ps -ef | grep mysqld
If MySql has been started, the output of the above command mysql process list, if mysql is not started, you can start the mysql server, use the following command:
root@host# cd /usr/bin ./mysqld_safe &
If you want to shut down the MySQL server is currently running, you can execute the following command:
root@host# cd /usr/bin ./mysqladmin -u root -p shutdown Enter password: ******
MySQL User Settings
If you need to add MySQL user, you only need mysql database to add new users to user tables.
The following examples of adding users, user name guest, password guest123, and authorized users can SELECT, INSERT and UPDATE operating authority:
root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed
mysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest',
PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host | user | password |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)
When you add a user, please note that use PASSWORD MySQL provided () function to encrypt passwords. You can see the user password encryption in the above examples: 6f8c114b58f2ce9e.
Note: password in the user table MySQL5.7 has replaced authentication_string.
Note: Note that you need to perform FLUSH PRIVILEGES statement. This command will reload the grant tables after execution.
If you do not use this command, you can not use the newly created user to connect to mysql server unless you restart the mysql server.
When you can create users, assign permissions to users, in the corresponding column permissions, set in the insert statement 'Y' can be, user rights are listed below:
- Select_priv
- Insert_priv
- Update_priv
- Delete_priv
- Create_priv
- Drop_priv
- Reload_priv
- Shutdown_priv
- Process_priv
- File_priv
- Grant_priv
- References_priv
- Index_priv
- Alter_priv
Another way to add users through the SQL GRANT command, your command will add the user to specify the database TUTORIALS zara, password zara123.
root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO 'zara'@'localhost'
-> IDENTIFIED BY 'zara123';
The above command will be in the mysql database user table to create a record of user information.
Note: MySQL SQL statement with a semicolon (;) as the end identifier.
/etc/my.cnf configuration file
Under normal circumstances, you do not need to modify the configuration file, which is the default configuration is as follows:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
In the configuration file, you can specify a different error log file directory, usually you do not need to change these configurations.
Management MySQL command
The following lists the Mysql database procedures commonly used commands:
USE database name:
Select Mysql database to operate, use this command after all Mysql command only for that database.mysql> use w3big; Database changed
SHOW DATABASES:
Lists MySQL database list database management system.mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | w3big | | cdcol | | mysql | | onethink | | performance_schema | | phpmyadmin | | test | | wecenter | | wordpress | +--------------------+ 10 rows in set (0.02 sec)
SHOW TABLES:
Display the specified database of all the tables, before using this command, use the command to select the database you want to use operation.mysql> use w3big; Database changed mysql> SHOW TABLES; +------------------+ | Tables_in_w3big | +------------------+ | employee_tbl | | w3big_tbl | | tcount_tbl | +------------------+ 3 rows in set (0.00 sec)
SHOW COLUMNS FROM data sheet:
For additional information about attribute display data table, property type, primary key information, whether it is NULL, default values, etc.mysql> SHOW COLUMNS FROM w3big_tbl; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | w3big_id | int(11) | NO | PRI | NULL | | | w3big_title | varchar(255) | YES | | NULL | | | w3big_author | varchar(255) | YES | | NULL | | | submission_date | date | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
SHOW INDEX FROM data sheet:
Display detailed information about the index data table, including the PRIMARY KEY (primary key).mysql> SHOW INDEX FROM w3big_tbl; +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | w3big_tbl | 0 | PRIMARY | 1 | w3big_id | A | 2 | NULL | NULL | | BTREE | | | +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)
SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \ G:
The command output performance and statistical information Mysql database management system.mysql> SHOW TABLE STATUS FROM w3big; # 显示数据库 w3big 中所有表的信息 mysql> SHOW TABLE STATUS from w3big LIKE 'w3big%'; # 表名以w3big开头的表的信息 mysql> SHOW TABLE STATUS from w3big LIKE 'w3big%'\G; # 加上 \G，查询结果按列打印
Gif presentation:
