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

1. Log into the database

[root@web01 data] # mysql-uroot-p

two。 Change the password of a known user

[root@web01 ~] # mysqladmin-uroot-p123456 password "oldboy"

3. Initialize the password for root

[root@web01 ~] # mysqladmin-uroot password "123456"

4. View the database

Mysql > show databases

5. Using the database

Mysql > use mysql

Database changed

Mysql >

6. View the tables in the database

Mysql > show tables

7. View the structure of the data table

Mysql > describe user

Mysql > show columns from test

8. Find fields from the table (the fields queried here are not case-sensitive)

Mysql > select Host,User from user; # look up the table directly

Mysql > select host,user from mysql.user; # which table from which library

9. Delete data from the library (users in the use database)

Mysql > drop user "sanlang" @ "10.0.0.8"

Mysql > drop user "@" 10.0.0.8 "

Or:

Mysql > delete from mysql.user where user= "sanlang" and host= "110.0.0.8"

10. View current users

Mysql > select user ()

11. View the current database

Mysql > select database ()

twelve。 Finish processing the commands to be executed by the user

Mysql > flush privileges

13. Export database

[root@web01] # mysqldump-uroot-poldboy123 wordexpress-B | gzip > bak.sql.gz

14. Import the database script

Decompress the backup before importing it into gunzip 2016-06-19.sql.gz

[root@web01] # mysql-uroot-poldboy123 grant all on wordpress.* to wordpress@'172.16.1.%' identified by '123456'

16. Remotely connect to the database

[root@web01] # mysql-ubbs-p123456-h 172.16.1.51

17. Log in to a multi-instance database

Mysql-uroot-poldboy123-h 10.0.0.7-P3306

18. Initialization password for multiple instances

[root@oldboy scripts] # mysqladmin-uroot password oldboy789-S / data/3308/mysql.sock

Summary of 19.mysql commands

-p,-- password [= name]

-S specifies socket

-P designated port

-h specify the remote IP

20. View help

Help creat database

21 View character set proofreading rules

Mysql > show character set

twenty-two。 Create the database oldboy of the GBK character set and view the complete statement for establishing the database

Mysql > create database oldboy CHARACTER SET gbk COLLATE gbk_chinese_ci

Mysql > show create database oldboy

23. View version

Mysql > select version ()

24 fuzzy query

Mysql > show databases like'% my%'

25. Add system and promote Wie Super Admin, that is, administrator users equivalent to root, but with different names

Mysql > grant all privileges on *. * to system@'localhost' identified by 'oldboy123' with grant option

This system user does not need to be added in advance. It will be added by default.

26 multiple instance users modify passwords

[root@oldboy 3306] # mysqladmin-usystem-p3306 password oldboy123-S / data/3306/mysql.sock

27 Update password

Mysql > UPDATE mysql.user SET password=PASSWORD ("123456") WHERE user='root' and host='localhost'

After updater

Mysql > flush privileges

twenty-eight。 View the command to create the library

Mysql > show create database oldboy

29.like statement

Mysql > show databases like 'oldboy%'

Mysql > show databases like'% oldboy%'

thirty。 Delete database

Mysql > drop database oldboy

thirty-one。 Do not jump out of mysql to execute linux command line commands

Mysql > system cd / root

Mysql > system ls

Anaconda-ks.cfg install.log install.log.syslog

Mysql >

thirty-two。 View the database you are currently in

Mysql > select database ()

thirty-three。 View current system time

Mysql > select now ()

thirty-four。 Create a user and authorize it (no permissions by default)

The first kind of authorization

Mysql > create user oldboy@'localhost' identified by 'oldboy123'

Mysql > grant all on oldboy.* to oldboy@'localhost'

The second type of authorization

Mysql > grant all on oldboyutf8.* to oldboy@'localhost' identified by 'oldboy123'; # it's okay if the user doesn't have to save here

Third type of authorization (example)

Mysql > grant all on oldboyutf8.* to oldboy@'10.0.0.%' identified by 'oldboy123'

Mysql > grant all on oldboyutf8.* to oldboy@'10.0.0.0/255.255.255.0' identified by 'oldboy123'; # cannot use 24, otherwise it will not take effect

Mysql > create user oldboy@'localhost' identified by 'oldboy123'

Mysql > grant all on oldboy.* to oldboy@'10.0.0.%'

thirty-five。 View the user's permissions

Mysql > show grants for oldboy@'localhost'

thirty-six。 Reclaim permission

Mysql > REVOKE INSERT ON oldboy.* FROM 'oldboy'@'localhost'

Recall oldboy users' insert permissions on the oldboy table

thirty-seven。 View the user's specific permission information

Mysql > select * from mysql.user\ G

thirty-nine。 View the creation of the table

Mysql > show create table student

forty。 View the structure of the database table

Mysql > desc student

forty-one。 View the columns of the table

Mysql > show columns from student

forty-two。 Delete primary key

Mysql > alter table student drop primary key; reflection: the self-added primary key cannot be deleted

Mysql > alter table student add primary key (id)

forty-two。 Remote connection

Mysql-uwordpress-poldboy123-h 172.16.1.51-P3306

forty-three。 View the specific permissions that the user has

Mysql > select * from mysql.db where user='wordpress'\ G

forty-four。 Create primary keys and indexes

Mysql > create table student (

-> id int (4) not null auto_increment

-> name char (20) not null

-> age tinyint (3) not null default'0'

-> primary key (id)

-> key index_name (name)

->)

->

Query OK, 0 rows affected (0.09 sec)

Mysql >

forty-five。 View the naming of the index

Mysql > desc student

Mysql > show index from student

Mysql > show index from student\ G; see more clearly

forty-six。 Delete index

View the name of the index:

Mysql > show index from student

+- -+

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+- -+

| | student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE |

| | student | 1 | index_name | 1 | name | A | 0 | NULL | NULL | | BTREE |

+- -+

2 rows in set (0.00 sec)

Mysql >

Mysql > alter table student drop index index_name

forty-seven。 Add an index to the table (the production scene is indexed at night)

Mysql > alter table student add index index_name (name)

Mysql > create index index_age on student (age)

forty-nine。 Create an index with the first n characters of a field of a table

Mysql > create index index_age on student (age (n))

Mysql > alter table student add index index_age (age (1))

fifty。 Create a federated index on multiple fields of the table (the more conditions, the fewer conditions are met)

Mysql > alter table stuent add index index_name_age (name (3), age (2))

Mysql > create index index_name_age on student (name,age)

Mysql > create index index_name_age on student (name (3), age (2)); the first few characters of the # field create a federated index

fifty-one。 De-weight the user (or remove weight on a specific column)

Mysql > select count (distinct user) from mysql.user

fifty-two。 Create a unique index

Mysql > create uniq index index_id_name on student (name)

fifty-three。 The operation of inserting data

Mysql > insert into student (id,name,age) values (1, "xiaoming", 10)

fifty-four。 Insert data in bulk

Mysql > insert into grade (name,age) values ("xiaoli", 90), ("sanlang", 93)

fifty-five。 Create a table test that specifies that its engine is innodb character set and gbk

Mysql > create table test (id int (4), name varchar (10)) engine=innodb charset=gbk

Mysql > alter table test ENGINE=MYISAM; # modify the engine of the table

fifty-six。 Update fields in a table

Mysql > update test set name= "oldgirl" where id=1

fifty-seven。 You insert a field at the back of a known table

Mysql > alter table test add age int (2) after name

fifty-eight。 Delete a field

Mysql > alter table test drop age; # Delete age field

fifty-nine。 Delete all data in the table

Mysql > truncate test; # Delete data files, physically delete, faster

Mysql > delete from test; # logical deletion, sql statement deletion is slow

sixty。 Query the first two rows of data

Mysql > select * from test limit 2; # query the first two rows of the table

Mysql > select * from test limit 1 from test limit 2; # query two after the first, excluding the first

sixty-one。 Query data and sort

Mysql > select * from test order by id desc

sixty-two。 Modify the type of field

Mysql > alter table test modify age char (4) after name

sixty-three。 Modify the name of the field

Mysql > alter table test change age oldboyage char (4) after name; # directly change the field name and field type

sixty-four。 Modify table name

Mysql > rename table oldtable to newtable

sixty-five。 Copy the structure of the table, when creating the database

Mysql > create table grade like sanlang

sixty-five。 The structure and data of the replication table when the data is created

Mysql > create table student select * from sanlang

sixty-six。 Non-interactive operation mysql database server

Single command

[root@oldboy] # mysql-uroot-poldboy123-S / data/3306/mysql.sock-e "show databases;"

Multiple commands:

[root@oldboy] # mysql-uroot-poldboy123-S / data/3306/mysql.sock-e "use backup;show tables;"

sixty-seven。 Commands for viewing the configuration of mysql

Mysql > show variables

[root@oldboy oldboy] # mysql-uroot-poldboy123-S / data/3306/mysql.sock-e "show variables like\" server_id\ ";

[root@oldboy oldboy] # mysql-uroot-poldboy123-S / data/3306/mysql.sock-e "show variables like 'server_id';" # View server_id

[root@oldboy oldboy] # mysql-uroot-poldboy123-S / data/3306/mysql.sock-e "show variables like'% log_bin%';" # bin_log log

[root@oldboy oldboy] # mysql-uroot-poldboy123-S / data/3306/mysql.sock-e "show variables like'% slow%';" # whether slow query is enabled

sixty-eight。 Do not restart the database, modify the configuration file of the database

If you do not restart the configuration file that modifies the data in the future, you will always do so.

[root@oldboy ~] # grep key_buffer / data/3306/my.cnf

Key_buffer_size = 16m

[root@oldboy ~] # set global key_buffer_size = 10240240032 # temporarily effective, restart mysql is invalid

Vim / data/3306/my.cnf

Key_buffer_size = 32m

[root@oldboy ~] # sed-I 's#key_buffer_size = 16M#key_buffer_size = 32M#g'/data/3306/my.cnf

(if not, add it manually)

sixty-nine。 Kill the process of the data sql statement

Kill ID

seventy。 View the database status information of the current how global status session

Mysql > show session status

seventy-one。 It is very important to view the running status information of the whole database. Do the analysis and monitor well.

Mysql > show global status; # you can view the database select,update. How many times?

Mysql > show global status like "% select%"; # check the number of times these commands are executed, which can be processed on a daily basis. Today minus yesterday is today's.

+-+ +

| | Variable_name | Value |

+-+ +

| | Com_insert_select | 21 | # number of inserts |

| | Com_replace_select | 0 | |

| | Com_select | 26 | # number of queries. Different queries will increase or decrease, but the same command will not increase. |

| | Select_full_join | 0 | |

| | Select_full_range_join | 0 | |

| | Select_range | 0 | |

| | Select_range_check | 0 | |

| | Select_scan | 58 | |

+-+ +

8 rows in set (0.00 sec)

Mysql >

seventy-two。 View the performance status of the innodb engine

Show engine innodb status

seventy-four。 View the target column

Mysql > show COLUMNS from test

seventy-five。 Modify primary key

Alter table student change id id int primary key auto_increment

seventy-six。 Sort sort and sort ascending

Mysql > select * from test order by id desc; # sort the order

Mysql > select * from test order by id asc; # sort in ascending order

seventy-seven。 Export data file

Mysql > select * from test order by id asc into outfile'/ tmp/file.txt'

seventy-eight。 Insert data in bulk

Mysql > insert into test (name,age) values ("xiaoming", 18), ("lili", 20)

seventy-nine。 Create a unique index

Mysql > create uniqe index index_ind_name on student (name)

Reflection: do not use kill-9 to stop the database, otherwise it will not be able to get up, and the consequences are very serious. It is best not to have special characters when creating the database.

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.