Common operations for MySQL

Common operations for MySQL

1. Login

Mysql-uroot-pdaixuan

2. View the database

mysql> show databases;

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

| Database |

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

| Information_schema |

| Discuz |

| MySQL |

| Test |

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

3. Switch database

Mysql>use Test

4. View current Database

Mysql>select database ();

5. View Current User

Mysql>select user ();

6. View the current database version

Mysql> select version ();

| 5.1.73-log |

7. View all the tables and see the details of a single table

Mysql>show tables;

Mysql> Show CREATE TABLE pre_ucenter_post\g;

8. Create a Database

Mysql>create database Daixuan;

Mysql>use Daixuan

9. CREATE table TB1 in database Daixuan, specify two fields int and char, specify character set GBK,

Mysql> CREATE TABLE TB1 (' id ' int (4), ' name ' char (+) ') Engine=myisam DEFAULT CHARSET=GBK;

Mysql>show tables; View Table

Mysql> Show CREATE TABLE tb1\g; View creation information for a table

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

Table:tb1

Create table:create Table ' tb1 ' (

' ID ' int (4) DEFAULT NULL,

' Name ' char (+) DEFAULT NULL

) Engine=myisam DEFAULT CHARSET=GBK

1 row in Set (0.00 sec)

10. Inserting data into the table TB1

mysql> INSERT INTO TB1 values (1, ' Daixuan ');

mysql> INSERT INTO TB1 values (2, ' aming ');

mysql> INSERT INTO TB1 (' id ') values (3); The ID uses a backslash

mysql> INSERT INTO TB1 (' name ') VALUES (' Linux '); name uses a backslash

11. View Table TB1

Mysql> select * from TB1;

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

| ID | name |

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

| 1 | Daixuan |

| 2 | aming |

| 3 | NULL |

| 4 | Linux |

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

4 rows in Set (0.00 sec)

12. Clear the table tb1

mysql> truncate TABLE daixuan.tb1;

13. Delete Table tb1

mysql> drop table tb1;

14. Delete Database Daixuan

mysql> drop Database Daixuan;

15, give Discuz database from 192.168.101. Any IP within the network segment to Daixuan user login

Mysql> Grant all on discuz.* to ' Daixuan ' @192.168.101.% ' identified by ' password '

16. Update the database

Mysql>flush privileges;

17. Display Database Queue

Mysql>show processlist;

18. View Variables

Mysql>show variables;

19, in MySQL set parameters and view, edit my.cnf save can be permanently effective

Mysql>set Global max_connectionns=200;

Mysql>show variables like ' max_connec% ';

20. View status (use when tuning)

Mysql>show status

Mysql>show status like '%running% ' pass with running

21. View MySQL error log in DataDir

[Email protected] ~]# Vim/etc/init.d/mysqld

Datadir=/data/mysql

[Email protected] ~]# Cd/data/mysql

[[email protected] mysql]# ls

Daixuan.err

[[email protected] mysql]# Tail daixuan.err View error log

22, how to repair the table

Mysql>repair table Discuz.pre_forum_post;

23. mysql Database backup

[Email protected] ~]# Mysqldump-uroot-pdaixuan discuz

[Email protected] ~]# Mysqldump-uroot-pdaixuan discuz >/data/discuz.sql

[Email protected] ~]# vim!$

Vim/data/discuz.sql

24. mysql Database recovery

If you accidentally delete a table of Discuz

[Email protected] discuz]# cd/data/mysql/discuz/

[Email protected] discuz]# RM-RF pre_forum_post*

[Email protected] discuz]#/etc/init.d/mysqld restart

Shutting down MySQL .... success!

Starting MySQL. success!

This time the website has not been opened properly

How to recover it?

[[email protected] discuz]# mysql -uroot-pdaixuan discuz </data/discuz.sql

25. Back up only the tables in the MySQL database

[Email protected] discuz]# mysqldump -uroot-pdaixuan discuz pre_forum_post >/data/ Discuz.post.sql

[Email protected] discuz]# vim!$

Vim/data/discuz.post.sql

26, restore the table in the MySQL database, note: Restore only the database name, no table name

[[email protected] discuz]# mysql -uroot-pdaixuan discuz </data/discuz.post.sql

27. Backing up and Restoring a database specifies a character set

[Email protected] discuz]# Mysqldump-uroot-pdaixuan--default-character-set=gbk discuz pre_forum_post >/ Data/discuz.post.sql

[Email protected] discuz]# Mysql-uroot-pdaixuan --default-character-set=gbk discuz </data/ Discuz.post.sql

This article is from the "Daixuan" blog, make sure to keep this source http://daixuan.blog.51cto.com/5426657/1722307

Common operations for MySQL