Operation and maintenance version MySQL additions and deletions change

Finishing is the best memory.

Operations frequently used SQL statements, long-term update ~~~~~

1. See which users

Mysql> select User,host from Mysql.user;

+------------+-----------+

| user | Host |

+------------+-----------+

| Root | 127.0.0.1 |

| Mysql_data | localhost |

| Root | localhost |

| Zabbix | localhost |

+------------+-----------+

2. View MySQL version number of bits

Mysql> select version ();

+-----------+

| Version () |

+-----------+

| 5.6.32 |

+-----------+

Mysql> Show variables like '%version_% ';

+-------------------------+------------------------------+

| variable_name | Value |

+-------------------------+------------------------------+

|                              slave_type_conversions | |

| version_comment | MySQL Community Server (GPL) |

| Version_compile_machine | i686 |

| Version_compile_os | Linux |

+-------------------------+------------------------------+

4 rows in Set (0.00 sec)

3. View current logged in user

Mysql> Select User ();

+----------------+

| User () |

+----------------+

| [Email protected] |

+----------------+

4. See if binary logs are turned on

Mysql> Show variables like "Log_bin";

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| Log_bin | OFF |

+---------------+-------+

5. View the default storage engine

Mysql> Show variables like ' storage_engine% ';

+----------------+--------+

| variable_name | Value |

+----------------+--------+

| Storage_engine | MyISAM |

+----------------+--------+

6. Database

mysql> CREATE DATABASE Zabbix;

Query OK, 1 row affected (0.12 sec)

Mysql> CREATE database Zabbix character set UTF8; ---> Create a database and set character sets

Query OK, 1 row Affected (0.00 sec)

mysql> show databases; ---> Display database

+--------------------+

| Database |

+--------------------+

| Information_schema |

| MySQL |

| Performance_schema |

| TestDB |

| Zabbix |

+--------------------+

mysql> use Zabbix; ---> Select a database

Database changed

Mysql> Show CREATE Database Zabbix; ---> View the complete statement of the build library

+----------+-----------------------------------------------------------------+

| Database | Create Database |

+----------+-----------------------------------------------------------------+

| Zabbix | CREATE DATABASE ' Zabbix '/*!40100 DEFAULT CHARACTER SET UTF8 */| |

+----------+-----------------------------------------------------------------+

mysql> drop Database Zabbix; ---> Delete a database

7. Data Sheet

Mysql> CREATE TABLE Zabbix (user_id int primary key,user_name varchar (), User_gender varchar (20)); ---> Create a table Zabbix

Query OK, 0 rows affected (0.43 sec)

Mysql> Show tables; ---> Show all Tables

+------------------+

| Tables_in_zabbix |

+------------------+

| Zabbix |

+------------------+

mysql> desc Zabbix; ---> Display table structure

+-------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------+-------------+------+-----+---------+-------+

| user_id | Int (11) | NO | PRI |       NULL | |

| User_name | varchar (20) |     YES | |       NULL | |

| User_gender | varchar (20) |     YES | |       NULL | |

+-------------+-------------+------+-----+---------+-------+

Insert into TableName (column 1, column 2 ....) Column n) values (value 1, value 2,.... Value N)

If no declaration is specified, all columns are inserted by default. Therefore, the value should correspond to all columns, in order one by one.

mysql> INSERT into Zabbix values (' 1 ', ' xiaoming ', ' Boy '); ---> Insert a column of data into a table

Mysql> select * from Zabbix;

+---------+-----------+-------------+

| user_id | User_name | User_gender |

+---------+-----------+-------------+

| 1 | Xiaoming | Boy |

| 2 | Laowang | Boy |

| 3 | Marong | Girl |

+---------+-----------+-------------+

Update table name Set column 1 = new value 1, column 2 = new value 2, column n = new value n .... Where * *

mysql> Update Zabbix set user_gender= ' Girl ' where user_id= ' 1 '; ---> Modify data

Mysql> select * from Zabbix;

+---------+-----------+-------------+

| user_id | User_name | User_gender |

+---------+-----------+-------------+

| 1 | Xiaoming | Girl |

mysql> truncate Zabbix; ---> Clear table data

Mysql> select * from Zabbix;

Empty Set (0.00 sec)

mysql> drop table Zabbix; ---> Delete a table

Query OK, 0 rows affected (0.05 sec)

Add primary Key

ALTER TABLE Tbname Add primary key (the column name where the primary key is located);

Example: ALTER TABLE goods add primary key (ID)

The example is to set the primary key on the ID column

To modify a table's delete primary key

ALTER TABLE tbname drop PRIMARY key;

To modify the table to increase the index

ALTER TABLE tbname add [unique|fulltext] Index name (column name);

Modifying a table's delete index

ALTER TABLE Tbname DROP INDEX index name;

8. Backing Up the database

Mysqldump-u root-p zabbix>/zabbix.sql----BACKUP Database Zabbix

MYSQL-UROOT-P zdj</mysql/zdj.sql----Recovery Database

Mysqldump-uroot-p--all-databases >all2.sql Back up all the libraries

Mysql-uroot-p <all2.sql Recover all the libraries

9. User Authorization Management

Format: Grant Permissions on database name. Table name To user @ Login host identified by "User password";

The IP address (or hostname) of the client that accesses MySQL is followed by any client, and if localhost is populated for local access (that user cannot remotely access the MySQL database).

Mysql> Grant all privileges on * * to [e-mail protected] '% ' identified by "123456";

Mysql> show grants for ZABBIX\G; ----View permissions to create a user

1. Row ***************************

Grants for [e-mail protected]%: GRANT all privileges on * * to ' zabbix ' @ '% ' identified by PASSWORD ' *6bb4837eb74329105ee456 8dda7dc67ed2ca2ad9 '

Mysql> Delete from Mysql.user where user= ' Zabbix ' and host= '% ';---> Delete user

10. View Library Size:

MariaDB [information_schema]> SELECT SUM (data_length) +sum (index_length) from Information_schema.tables WHERE TABLE _schema= ' drcom ';

+------------------------------------+

| SUM (data_length) +sum (index_length) |

+------------------------------------+

| 26051771392 |

+------------------------------------+

1 row in Set (0.42 sec)

The result is in bytes, except that 1024 is K, except that 1048576 is M.

11. View the total number of tables:

SELECT COUNT (table_name) from INFORMATION_SCHEMA. TABLES WHERE table_schema= ' drcomweixin ';-->

12. View the table size:

SELECT table_name,data_length+index_length,table_rows from Information_schema.tables WHERE table_schema= ' library ' and TABLE _name= ' table name ';

13.show processlist;

http://renxiangzyq.iteye.com/blog/835397

Number of MySQL Threads

[Email protected]/]# mysqladmin processlist-uroot-p |wc-l

Enter Password:

24

14. View the maximum number of connections

[Email protected]/]# mysql-uroot-p-e "show variables like '%max_connections% ';"

Enter Password:

+-----------------------+-------+

| variable_name | Value |

+-----------------------+-------+

| extra_max_connections | 1 |

| max_connections | 1000 |

+-----------------------+-------+

15. View the current number of connections

[Email protected]/]# mysql-uroot-p-e "show status like ' threads% ';"

Enter Password:

+-------------------+-------+

| variable_name | Value |

+-------------------+-------+

| threads_cached | 5 |

| threads_connected | 16 |

| threads_created | 1697 |

| threads_running | 3 |

+-------------------+-------+

16.mysql Viewing status

Mysql> Show status;

[Email protected] ~]# mysqladmin-uroot-p***** status

Warning:using a password on the command line interface can is insecure.

uptime:8135940 threads:17 questions:117931987 Slow queries:0 opens:215 Flush tables:1 Open tables:208 Qu Eries per second avg:14.495

~~~~~ not to be continued

This article is from the "unplug the Operational Space" blog, please be sure to keep this source http://zhangdj.blog.51cto.com/9210512/1878480

Operation and maintenance version MySQL additions and deletions change