MySQL Common operations

MySQL Common operations

Change root password

First check that the MySQL service has no boot: PS aux |grep MySQL

Did not start to start it:/etc/init.d/mysqld start

Then use the command to log in to MySQL: Mysql-uroot quit

MySQL command cannot be used directly under the root user, because he is in the/usr/local/mysql/bin/mysql directory,

Need to change environment variable path, add MySQL absolute path:export path= $PATH:/usr/local/mysql/bin/

To be permanent, you need to put it in theVi/etc/profile config file and put it on the last side.

Execution:source/etc/profile environment variable to take effect

Command: Mysql-uroot-p-P: Specify his password, and if the password is empty, enter it directly.

How to set the root password:

Mysqladmin-uroot password ' 123456789 ' (for example we set 12345789)

How to enter the password after Setup:

Method One: Mysql-uroot-p then enter the set password, example 123456789-p parameter means to specify his password

Method Two: Mysql-uroot-p ' 123456789 ' give him the password directly

Change Password:

For example, our original password for 123456789 changed to 987654321, the format is as follows

Mysqladmin-uroot password ' 123456789 ' password ' 987654321 '

Password reset

The first step is to change the configuration file: vi/etc/my.cnf//Add skip-grant means ignore authorization, that is, when you operate MySQL, omit to enter the password this step

Step two, restart the MySQL service:/etc/init.d/mysqld restart

The third step is to log in to the MySQL command: mysql-uroot just come in. No Password required

Fourth step, switch to the MySQL library to change a table: use MySQL;

Fifth step, enter command:update user set Password=password ('aminglinux') where user= ' root '; Inside the horizontal line part aminglinux is the new password that you want to set, the quit quit after the change

The sixth step, in the editor: Vi/etc/my.cnf to remove skip-grant because it's been added. Other users don't need a password so it's not safe.

Seventh step, restart:/etc/init.d/mysqld start

Connect to MySQL

There are several methods:

1. Connect the machine : mysql-uroot-p123456//-u user name;-p password

2. Connect the remote

For example, with a server to link B server MySQL, you need to enter the remote IP, port.

- h option , remote MySQL IP. - p option , which means specifying a port

For example, use the native connection 3306 port: mysql-uroot-p123456-h127.0.0.1-p3306

3. Using the socket to link It, this method is only suitable for the local, because MySQL listens to the 3306 port with the socket. -S to specify its socket

Mysql-uroot-p123456-s/tmp/mysql.sock

4. Link Mysal to manipulate some commands

mysql-uroot-p123456- E "Show Databases" - e means to list all databases, which is used in shell scripts

MySQL Common commands

The command to run MySQL must first connect MySQL, run these commands in MySQL, outside is not recognized, the same command outside is not recognized inside.

Go to mysql command: mysql-uroot-p123456789

1. Query library: Show databases

show databases; see what databases are available

2. Switch libraries: Use

use MySQL; switch to a library below, for example MySQL library

3. View the tables in the library:

Show tables; list all the tables.

4. View the fields in table: desc

Desc user, followed by the table you want to query, such as DESC user;

5. View how the table was created: Show CREATE TABLE

Show CREATE TABLE user\g; Command behind the table you want to view, such as the user table, \g means its vertical display

6. View Current User: Select User ();

Select User (), because the default login is root

7. View the database currently in use: select databases ();

Select Database ();

Switch on the view

8. Creating a library: Create database;

Create Database db1; (DB1, is the name of his own library)

You found one more db1.

9. Create a table:

First enter into the library inside use DB1; In creating a table (for example, creating a table called T1): Create TABLE T1 (' id ' int (4), ' name ' char (40));

To define a field after creating the table, the first field is called the ID, and the format is the maximum of 4 for Init. The second field is called the Name,char string with a maximum of 40

You can also define the fields that are behind

10. Delete the table:

The name of the table behind the drop table

11. View the current database version:

Select version ();

12. View the status of the database: Show status;

13. View each parameter: show variables;

14. View the specified parameters

For example, you want to view but don't remember, only know the front, then you can use a wildcard to match%, for example: show variables like 'max_connect%';

For example, slow related

15. Modify the Parameters:

Set global max_connect_errors=1000; For example, modify max_connect_errors to 1000

16. View the queue:

Show Processlist; The equivalent of using PS or top in Linux to view the process, you can see which users are connected to it, what the user is doing, it is important

more complete view: Show fullprocesslist;

MySQL create user and authorize

How to set up a new user and password

Grant all on * * to ' user1 ' identified by ' passwd '; User1 is the user; passwd means the password you set yourself.

Created: Grant all on * * to ' user1 ' @ ' 127.0.0.1 ' identified by ' passwd ';

Login: mysql-uuser1-ppasswd-h127.0.0.1 Because he uses the socket by default, you specify the-H

Give him authorization, localhost, so you don't have to specify IP for H.

Grant all on * * to ' user1 ' @ ' localhost ' identified by ' passwd ';

mysql-uuser1-ppasswd

Authorize according to specific permissions: Grant Select,update,insert on db1.* to ' user2 ' @ ' 192.168.133.1 ' identified by ' passwd ';

Authorization for all IPs: Grant all on db1.* to ' User3 ' @ ' percent ' identified by ' passwd ';

View all authorizations: show grants; The default view is root, because root is not authorized so it is empty

View authorization for a specified user

Need to specify user plus ip:show grants for [email protected];

Common SQL statements

How to view tables in other libraries:select COUNT (*) from Mysql.user; For example, view the user table in the MySQL library

View all content in the library:select * from mysql.db; This * generally do not use, because there are many things in the library, time-consuming

View a field: Select db from Mysql.db;

View two fields: Select Db,user from Mysql.db;

Fuzzy query: SELECT * from mysql.db where host like ' 192.168.% ' \g; for example, the query starts with 192.168. IP field

Insert content data: INSERT into DB1.T1 values (1, ' abc ');

Name is a string as much as possible to add the single quotation mark ", the number can not be added

Three lines are in effect: Update db1.t1 set name= ' AAA ' where id=1;

Delete the table with the specified ID: delte from db1.t1 where id=1;

Clear a table:truncate table db1.t1; Only the contents of the table are emptied, and the structure of the table remains. The name behind the table, such as the T1 table in the DB1 library.

Delete tables directly: Drop table db1.t1;

The database has also been deleted: drop databases db1;

Note: Delete table and clear table operation try not to do!!!!!!!!!!!!!!!!!!

Backup and recovery of MySQL database

Backup library:mysqldump-uroot -p123456 mysql > /tmp/mysql.sql-u designated User;-p specify password; back with the library you are backing up (e.g. MySQL), Then redirect the content to a file, this file is the library file we backed up

Recovery Library mysql-uroot -p123456 mysql < /tmp/mysql.sql

can also revert to other libraries, such as MYSQL2, we first create a mysql2 library, command:

MYSQL-UROOT-PAMINGLINUX-E "CREATE Database Mysql2"

And then restore it to the past.

Backup table mysqldump-uroot -p123456 mysql user > /tmp/user.sql in the library with the table name on the line, the first library in the table, For example, the user table in the MySQL library

Recovery table mysql-uroot -p123456 mysql < /tmp/user.sql just need to write a library name, you don't need to name the table.

Back up all libraries mysqldump-uroot-p-A >/tmp/123.sql-a represents all

Back up table structure only mysqldump-uroot -p123456- d mysql >/tmp/mysql.sql

MySQL Common operations