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

This article mainly introduces how to add, delete, change and check the operation and maintenance version of MySQL. The content of the article is carefully selected and edited by the author. It has a certain pertinence and is of great significance to everyone's reference. Let's work with the author to understand how to add, delete, change and check the lower operation and maintenance version of MySQL.

1. Check which users there are.

Mysql > select user,host from mysql.user

+-+ +

| | user | host |

+-+ +

| | root | 127.0.0.1 | |

| | mysql_data | localhost |

| | root | localhost |

| | zabbix | localhost |

+-+ +

2. Check the number of digits of mysql version

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 the currently logged in user

Mysql > select user ()

+-+

| | user () |

+-+

| | root@localhost |

+-+

4. Check whether the binary log is turned on

Mysql > show variables; will display all the mysql parameters

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. Create a 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 the character set

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 building a library

+-+ +

| | Database | Create Database |

+-+ +

| | zabbix | CREATE DATABASE `zabbix` / *! 40100 DEFAULT CHARACTER SET utf8 * / | |

+-+ +

Mysql > drop database zabbix;-- > Delete the database

7. Data sheet

Mysql > create table zabbix (user_id int primary key,user_name varchar (20), 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 there is no declaration, all columns are inserted by default. Therefore, the value should correspond to all columns one by one in order.

Mysql > insert into zabbix values);-> insert a column of data into the 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;-- > emptying table data

Mysql > select * from zabbix

Empty set (0.00 sec)

Mysql > drop table zabbix;-- > Delete the table

Query OK, 0 rows affected (0.05 sec)

Mysql > desc zabbix.hosts

+-+ +

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

+-+ +

| | hostid | bigint (20) unsigned | NO | PRI | NULL |

| | proxy_hostid | bigint (20) unsigned | YES | MUL | NULL |

| | host | varchar (128) | NO | MUL | |

| | status | int (11) | NO | MUL | 0 | |

. Multiple lines are omitted here

Mysql > select host from zabbix.hosts

+-+

| | host |

+-+

| | AC-1 |

| | AC-2 |

.

Mysql > select host,status from zabbix.hosts

+-+ +

| | host | status |

+-+ +

| | Template OS Linux | 3 | |

| | Template App Zabbix Server | 3 | |

| | Template App Zabbix Proxy | 3 | |

| | Template App Zabbix Agent | 3 | |

| | Template SNMP Interfaces | 3 | |

.

Mysql > select host,status from zabbix.hosts where status like'0'

+-+ +

| | host | status |

+-+ +

| | ddd | 0 | |

| | {# VM.UUID} | 0 | |

| | {# HV.UUID} | 0 | |

| | zabbix server | 0 | |

.

Increase primary key

Alter table tbName add primary key (column name of the primary key)

Example: alter table goods add primary key (id)

This example builds the primary key on the id column

Modify the delete primary key of the table

Alter table tbName drop primary key

Modify the added index of the table

Alter table tbName add [unique | fulltext] index index name (column name)

Modify the deleted index of a table

Alter table tbName drop index index name

8. Backup database

Mysqldump-u root-p zabbix > / zabbix.sql-backup database zabbix

Mysql-uroot-p zdjall2.sql backs up all libraries

Mysql-uroot-p grant all privileges on *. * to zabbix@'%' identified by "123456"

Mysql > show grants for zabbix\ G;-"View the permissions to create a user.

* * 1. Row *

Grants for zabbix@%: GRANT ALL PRIVILEGES ON *. * TO 'zabbix'@'%' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

Mysql > delete from mysql.user where user='zabbix' and host='%';--- > Delete a user

10. View the 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 1024 for K and 1048576 for M.

11. View the total number of tables:

SELECT count (TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA='drcomweixin';-- >

Mysql > select count (*) from mysql.user; to see how many rows there are in the user table

+-+

| | count (*) |

+-+

| | 7 |

+-+

1 row in set (0.00 sec)

twelve。 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

[root@vps /] # mysqladmin processlist-uroot-p | wc-l

Enter password:

twenty-four

14. View maximum number of connections:

[root@vps /] # 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:

[root@vps /] # 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 View status:

Mysql > show status

[root@vps] # mysqladmin-uroot-p * status

Warning: Using a password on the command line interface can be insecure.

Uptime: 8135940 Threads: 17 Questions: 117931987 Slow queries: 0 Opens: 215 Flush tables: 1 Open tables: 208 Queries per second avg: 14.495

17. View the currently used libraries:

Mysql > select database ()

+-+

| | database () |

+-+

| | mysql |

+-+

1 row in set (0.00 sec)

18. Change the password:

Mysql > update mysql.user set password=password ('123456') where user='root'

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3 Changed: 3 Warnings: 0

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

After reading the above about the operation and maintenance version of MySQL how to add, delete, change and search, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

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.