MySQL Basic Operations Command

Summary of commands: SS, Killall, Kill, Pkill

To close the database method:

Mysqladmin-uroot-ppassword shutdown

/etc/inti.d/mysqld stop

KILL-USR2 $ (cat path/pid)

To change the MySQL data logon prompt:

1.mysql> prompt \[email protected] \r:\m\s->

PROMPT set to ' \[email protected] \r:\m\s-> '

[Email protected] 10:41:22->

2.vim my.cnf

[MySQL]

Prompt=\\[email protected] \\r:\\m:\\s->

Using MySQL help, it's awesome ...

To set the root password:

Mysqladmin-uroot password ' SetPassword '

Change Password:

Mysqladmin-uroot-p ' oldpassword ' password ' newpassword '-s/data/3306/mysql.sock

Password change for forgotten root password:--skip-grant-tables

Mysqld_safe--defaults-file=/data/3306/my.cnf--skip-grant-tables &

Mysqld_safe--skip-grant-tables--user=mysql &

Update Mysql.user set Password=password (' NewPassword ') where user= ' root ' and host= ' localhost ';

Flush privileges;

No root password:

Mysql> set Password=password (' SetPassword ');

mysql> flush Privileges;

Use of SQL statements:

DQL (data Query Language), DML (Data Manipulation language), TPL (transactional language), DCL (Data Control Language), DDL (data definition Language), CCL (pointer control language)

Dql:select

Dml:insert, UPDATE, delete

Tpl:begin, transaction, Commit, rollback

Dcl:grant, REVOKE

Ddl:create, Drop

Ccl:declare cursor, fetch into, update where current

Select Database ();

Select User ();

Drop Delete When deleted, generally refers to the user.

Create a user and authorize:

Grant all privileges the dbname.* to [e-mail protected] identified by ' password ';

Flush privileges;

To view User rights:

Show grant for [email protected];

To reclaim user rights:

Revoke insert on dbname.* from [email protected];

User authorization for the production environment:

1, blog, CMS:

Select, insert, UPDATE, delete, create, drop are required during installation. Recover Create and drop after installation is complete.

Under normal circumstances: SELECT, insert Update, delete.

2, Bbs,discuz:

All need Select, INSERT, UPDATE, delete, create, drop

For master-Slave Authorization:

Main Library (write as Primary read supplement):

This machine: Lnmp,lamp

Grant all privileges on blog.* to ' blogs ' @ ' localhost ' identified by ' blog ';

The application server and database server are not authorized on one host:

Grant all privileges in ' blog '. * to ' blog ' @ ' hostname ' identified by ' blog ';

Strict authorization: Pay attention to safety, ignoring convenience:

Grant SELECT, INSERT, UPDATE, delete on ' blog '. * to ' blog ' @ ' hostname ' identified by ' blog ';

From library (read-only):

Grant select on ' Blogs '. * to ' blogs ' @ ' hostname ' identified by ' blog ';

Master-Slave Advanced Authorization policy:

The first type:

Write Library: Blog Password:password port:3306 hostname:10.0.0.x

Read library: Blog Password:password port:3306 hostname:10.0.0.x

The second type:

Write library: blog_w Password:password port:3306 hostname:10.0.0.x

Read the library: Blog_r Password:password port:3306 hostname:10.0.0.x

Specific commands for authorization:

Main Library:

Grant SELECT, INSERT, UPDATE, delete on ' blog '. * to ' blog ' @ ' 10.0.0.x ' identified by ' blog ';

From library:

Grant select on ' Blogs '. * to ' blogs ' @ ' 10.0.0.x ' identified by ' blog ';

In addition to making a select from the library, you can also add read-only parameters, strictly control the Web user write from the library.

MySQL libraries and tables in the master and slave libraries are synchronized and cannot authorize different permissions for the same user. Because the primary library is automatically synchronized to the slave library after authorization, the authorization read-only failure from the library.

Resolve Funciton:

1. Cancel the synchronization of the MySQL library.

2. After authorizing the main library permissions, perform the retract and add-up from the library.

3. Do not control additions and deletions on the authorization, but use the read-only parameter, control the normal user update from the library, note that the read-only parameter is not valid for the super user.

To view MySQL user authorization:

Show grant for ' username ' @ ' hostname ';

Operation of the data table:

CREATE TABLE Tname (

Field type,

Field type,

field type);

To create an index for a table's fields:

CREATE TABLE Tname (

Field type Auto_increment,

Field type,

Field type

Primary key (ID),

Key Index_name (name)

);

To delete a primary key:

ALTER TABLE tname drop PRIMARY key;

Add primary key:

ALTER TABLE TNAME change ID ID int primary key auto_increment;

To delete an index:

ALTER TABLE student DROP INDEX index_name;

To add an index to a column:

ALTER TABLE Tname Add index index_name (name);

Create a normal index on the first n characters of a field:

Create INDEX index_name on Tname (Col_name (8));

To create a federated index on multiple fields of a table:

Create INDEX index_name on Tname (col_name1, col_name2);

Show Index Form Tname\g

To delete an index:

Drop index index_nanme on tname;

Create a federated index on the first n character columns of multiple fields of a table:

Create INDEX index_name on Tname (col_name1 (8), col_name2 (10));

When querying by criteria, the Federated index is prefixed with the attribute.

Index (A,B,C) only A, AB, ABC three query criteria list can go index. b, BC, AC, C etc. cannot go index.

To create a unique non-primary key index:

Create unique index index_name on tname (col_name);

Small tables do not need to be indexed, write frequently, and read fewer businesses to build fewer indexes.

Select User,host from Mysql.user where host= .... The index must be created in the Criteria column instead of the Select data column, and we try to make an index on a large table with a unique value.

To create a primary key index:

ALTER TABLE TNAME change ID ID int primary key auto_increment;

To delete a primary key index:

ALTER TABLE tname drop PRIMARY key;

To create a normal index:

ALTER TABLE Tname Add index index_name (col_name (8));

Create INDEX index on Tname (Col_name (8));

Create INDEX index_name on Tname (col_name1, col_name2);

To delete a normal index:

ALTER TABLE Tname DROP INDEX index_name;

Alter and create help related

Help ALTER TABLE;

Insert data:

Insert into Tname (col1, col2 ...) VALUES (value1, value2 ...);

Query the execution plan for the SELECT statement:

Explain select * from Tname;

Help explain

Manuals need to be mastered: 5, 6, 7, 8, 10, 11, 13, 14, 15

View the MySQL binary log using the Mysqlbinlog command to view the binary log files. Binary log files only record changes and do not log queries.

# Mysqlbinlog Mysql-bin.log

Ideas and methods for recovering data using binary logs:

First, use the full backup of the database to recover the data at that time of the full backup, and then use the binary log to recover data from the full backup to the time period of the data error. The command is as follows:

Mysql-uroot-proot dbname </path/to/dbname.sql

Mysqlbinlog mysql-bin.000001 > Mysql-bin.sql

In the Mysql.sql file to find the relevant error time error statement, delete it. In the recovery to dbname, you can

Mysql-uroot-proot Dbnmae </path/to/mysql-bin.sql

Recovery is complete.

Prevent MySQL from misoperation: Log in using the Mysql-u command.

To delete table data:

Delete from Tname; the data in the table is deleted in one article.

TRUNCATE table tname; Drop the entire table first, and then recreate the table. (High efficiency)

Adding and Deleting Columns (field):

ALTER TABLE Tname Add/change ....

Change table name:

Rename table Old_tname to New_tname;

SQL statement Focus: Build, build, Index, column

5 ways to solve MySQL garbled:

1. Log in after MySQL execution: set names UTF8;

2. Specify in the SQL file: set names UTF8; Login MySQL execution: source Dbname.sql

3. Specify in the SQL file: set names UTF8; Using MySQL import: Mysql-uroot-proot dbname < Dbname.sql

Mysql-uroot-proot-e "Set names UTF8; SELECT * from Dbname.tname; "

4. Import the specified character set via MySQL: Mysql-uroot-proot--default-character-set=utf8 dbname < Dbname.sql

5. Modify the configuration file: Vim my.cnf

[Client]

Default-character-set=utf8

[Mysqld]

Default-character-set=utf8 Version 5.1

Character-set-server=utf8 version 5.5

Linux, client, server, library, table, program, use the same character set.

Show variables;

Show global status;

Set Global key_buffer_size=

Show vairables like ' key_buffer% ';

MY.CNF Plus key_buffer_size=

Mysql-e

Mysqldump, Mysqlbinlog, MySQL, mysqladmin

To modify the character set of a MySQL database table:

ALTER DATABASE Character Set *

ALTER TABLE Tname Character set *

The existing data table cannot be modified, only the newly created ones can be modified.

To modify the character set of an existing data table, you need to export the data and then re-import it after modifying the character set:

ALTER DATABASE dbname character set UTF8;

1. Export the table structure:

Mysqldump-uroot-p--default-character-set=gbk-d dbname > Dbname.alltable.sql

The--DEFAULT-CHARACTER-SET=GBK represents the GBK connection, and-D only exports the table structure.

2. Edit Dbname.sql to change the original character set to the current character set

3. Ensure that the database is no longer updated, export all data

Mysqldump-uroot-p--quick--no-create-info--extended-insert--default-character-set=utf8 dbname > Alldata.sql

--quick: Used to dump a large table, forcing mysqldump to retrieve data from the server one row at a time instead of retrieving all rows and outputting the front cache into memory.

--no-create-info: The CREATE TABLE statement is not created.

--extended-insert: Use the multiple-line insert syntax, which includes several values lists, so that the file is smaller, the IO is small, and the data is imported faster.

--default-character-set=utf8: Export Data according to the original character set, so that all the Chinese to be visible in the exported file will not be saved as garbled.

4. Edit Alldata.sql to change set names UTF8 to GBK

5. Building a library

Create DATABASE dbname default CharSet GBK;

6. Create a table and execute Dbname.alltable.sql

Mysql-uroot-proot dbname < Dbname.alltable.sql

7. Import data:

Mysql-uroot-proot dbname < Alldata.sql

To back up all the databases with mysqldump command:

Mysql-uroot-proot-e "show databases;" | Grep-evi "Database|infor|perfor" | Sed-r ' s#^ ([a-z].*$) #mysqldump-uroot-proot-b \1|gzip >/opt/\1.sql.gz#g ' | Bash

MySQL Basic Operations Command