Common operations for MySQL

1. Query: Log, DB, Tables, descript

Show master logs; # #查看日志 the/usr/local/mysql/data/directory; for mysql5.5

show databases; # #查看所有数据库

Show tables; # #查看当前数据库的所有表

Use database name; # #进入一个数据库

DESC table name; # #查看一个表的结构

2. New, delete db, Tables

Create database name; # #创建一个新的数据库

CREATE table database name. Table name (field 1 type, field 2 type NOT NULL ..., primary key (field name)); # #创建表, names use letters, _, numbers, do not use $, #,-etc characters

drop table name; # #删除一个表

drop database name; # #删除一个数据库

3. Manage records in tables: SELECT, INSERT, UPDATE, delete

Syntax: Select field from table name where expression; # #语句最后跟 "\g" can be easily viewed

Syntax: INSERT into database name. Table name (field 1 type,...) values (the Value of field 1,...);

Syntax: Update database name. Table Name Set field 1= value, ... where conditional expression

Syntax: Delete from database name. Table name where conditional expression

1) root of MySQL forgot password:

Sed-i '/\[mysqld\]/a skip-grant-tables '/etc/my.cnf # #设置跳过权限

/etc/init.d/mysqld restart

MySQL # #登录

Update Mysql.user Set Password=password ("123123") where user= ' root '; # #修改root用户密码

Flush privileges; # #更新权限

Oy4. Rights Management: Grant, show grants, REVOKE

Syntax: Grant permission list on database name. Table name to ' user ' @ ' client IP ' identified by ' password ';

Syntax: Show grants for ' user ' @ ' client IP ';

Syntax: Revoke permissions list on database name. Table name from ' user ' @ ' client IP ';

5. Backup and restore: mysqldump, MySQL

1) Cold backup:

/etc/init.d/mysqld stop

tar-jcvf/opt/mysql_bak-$ (date +%f). Tar.xz/usr/local/mysql/data

Recovery:

Tar-jxvf/opt/mysql_bak-*.tar.xz-c/usr/local/mysql/data

2) Hot backup:

Mysqldump-u User name-p password--databases database name > backup file name # #备份单个数据库

Mysqldump-u User name-p password--opt--all-databases > backup file name # #备份整个数据库

Mysqldump-u User name-p password--opt--all-databases--lock-talbes=0 > backup file name

Recovery:

Mysql-u User name-p password < backup file name

3) operate MySQL in base:

Mysql-u User name-p password <<end

Statement

END

Common operations for MySQL