MySQL Basic Operation explained

1. Several ways to log in to MySQL:

#mysql-uroot-p123456 to log in using the socket connection;

View Socket file Location Ps-aux | grep mysqld

[[Email protected] ~]# PS aux |grep mysqldroot 941 0.0 0.1 6268 1416? S 21:01 0:00/bin/sh/usr/local/mysql/bin/mysqld_safe--datadir=/data/mysql--pid-file=/data/mysql/localhost.localdo        Main.pidmysql 1056 0.0 4.4 392208 45456? Sl 21:01 0:01/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/data/mysql--user=mysql--log-error=/ Data/mysql/localhost.localdomain.err--pid-file=/data/mysql/localhost.localdomain.pid--socket=/tmp/mysql.sock-- port=3306

method to specify the location of the socket file:/ETC/MY.CNF configuration file, compile time

#mysql the path to the-uroot-p123456-s/socket file, log in using the specified socket file;

#/usr/local/mysql/bin/mysql-uroot-p123456-s/tmp/mysql.sock

mysql-uroot-p123456 -h127.0.0.1-p3306 use IP address 3306 port connection, the port can be customized;

#/usr/local/mysql/bin/mysql-uroot-p123456-h127.0.0.1-p3306

Executes MySQL statements directly inside the shell and requires the-e parameter

# mysql-uroot-p123456- E "Use Mysql;select host,user from user where user= ' root ';

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m01/6c/ac/wkiom1vpyr6go7i9aae_hvfikeg388.jpg "title="-e.jpg "alt=" wKiom1VPYr6gO7i9AAE_ Hvfikeg388.jpg "/>

View the command history performed on MySQL Cat/root/.mysql_history

First delete the file and then point the file Soft link to the black hole to prevent the command from leaking.

Ln-s/dev/null/root/.mysql_history

2, the common operation of MySQL:

View all gallery show databases;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m00/6c/ac/wkiom1vpy7cia8t0aacoqsvytsm768.jpg "title=" 12.jpg "alt=" Wkiom1vpy7cia8t0aacoqsvytsm768.jpg "/>

View a library's table use DB; show tables; DB is the name of the database listed in the database;

mysql> use Discuz;

Mysql> Show tables;

View the table's fields desc TB; TB is the name of the table listed in tables;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m01/6c/a7/wkiol1vpzagglzrbaaibyxilgbu286.jpg "title=" desc.jpg "alt=" Wkiol1vpzagglzrbaaibyxilgbu286.jpg "/>

View the Build Table statement show create TABLE TB; You can copy the commands yourself to create other tables, you can view the default engine for the table, and the default character set;

Write-only table names are below the database, or they can be libraries. Table to view tables of other libraries;

# Show CREATE table pre_home_show;

# Show CREATE table Mysql.user;

Engine=myisam DEFAULT Charset=utf8

View which user is currently Select users ();

View the currently located library Select Database ();

Creating a library Create database db1;

CREATE TABLE T1 (' id ' int (4), ' name ' char (40));

View database version select version ();

View MySQL status show status;

View MySQL-provided storage engine show engines;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" border:1px solid rgb (221,221,221); background-position:50% 50%; "alt=" Spacer.gif "/>650" this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6C/AC /wkiom1vpyndx9igcaas-q5_pnnm944.jpg "title=" engines.jpg "alt=" Wkiom1vpyndx9igcaas-q5_pnnm944.jpg "/>

To modify the MySQL parameters:

See all the variables show variables;

Show variables like ' max_connect% '; % represents a wildcard character, as with *, multiple arbitrary characters;

Show variables like '; The quotation marks are empty, you can also display all the variables, quotation marks can write keywords plus%, to view the items of keywords;

Modifying the value of a parameter     Set Global max_connect_errors = 1000;

Mysql> Show variables like ' max_connect% ';

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

| variable_name | Value |

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

| max_connect_errors | 10 |

| max_connections | 151 |

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

2 rows in Set (0.01 sec)

Mysql> set global max_connect_errors=10000;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like ' max_connect% ';

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

| variable_name | Value |

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

| max_connect_errors | 10000 |

| max_connections | 151 |

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

2 rows in Set (0.00 sec)

View the current default storage engine show variables like '%storage_engine% ';

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m00/6c/a7/wkiol1vpy9dwmovwaadk2tgl_8e093.jpg "title=" storage_engine.jpg "alt=" Wkiol1vpy9dwmovwaadk2tgl_8e093.jpg "/>

The contents of the database stored under the/data/mysql/directory, and the directory corresponding to the database name;

MyISAM stores a file in 3 formats (. frm. MYD. MYI)

view MySQL queue show processlist; Show full processlist; display all the information.

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m02/6c/a7/wkiol1vpz2-jdwzoaaf--rjnkiy575.jpg "title=" 11.jpg "alt=" Wkiol1vpz2-jdwzoaaf--rjnkiy575.jpg "/>

Create a regular user and authorize

Grant all on * user1 identified by ' 123456 ';

Grant all on db1.* to ' user2 ' @ ' 10.0.2.100 ' identified by ' 111222 ';

Grant all on db1.* to ' User3 ' @ ' percent ' identified by ' 231222 ';

Change Password UPDATE mysql.user SET Password=password ("Newpwd") WHERE user= ' username ';

Enquiry show the number of rows in a table    Select COUNT (*) from Mysql.user; MyISAM display speed is relatively fast;

Querying all the contents of a table select * from Mysql.db;

The contents of the Conditional query table select * FROM Mysql.db where the host like ' 10.0.% ';

Mysql> select * from Mysql.db\g; If the display garbled, need to add \g, list display;

updated record update db1.t1 set name= ' AAA ' where id=1;

mysql> Update huang.name set name= ' AAA ' where id=1;

Query OK, 0 rows affected (0.01 sec)

Rows matched:0 changed:0 warnings:0

Delete table Data Delete form TB where id=1;

Empty tables truncate TABLE db1.t1;

Delete tables drop table db1.tb1;

Delete database drop databases db1;

Fix tables Repair table tb1 [use frm]; [Use frm] is an index file, the index is rebuilt;

3. mysql Backup and recovery

Use mysqldump Backup, suitable for small amount of data;

Backup mysqldump-uroot-p123456 db >/tmp/1.sql Redirect to a file inside;

Recover mysql-uroot-p123456 db < 1.sql reverse Redirect, equivalent to replicating a database; DB must be real;

Back up only one table Mysqldump-uroot-p db TB > 2.sql

Recover a table mysql-uroot-p123456 db < 2.sql

mysqldump-uroot-p123456- D db TB > 3.sql only statements that back up tables;

Specify character set mysqldump-uroot-p123456--default-character-set=utf8 db >1.sql specified character set when backing up to prevent garbled characters;

Restore also specifies the character set mysql-uroot-p123456--default-character-set=utf8 db < 1.sql

This article is from the "Model Student's Learning blog" blog, please be sure to keep this source http://8802265.blog.51cto.com/8792265/1650084

MySQL Basic Operation explained