Simple operation of MySQL under Linux

Change the password of the MySQL database root

The first entry into the database is not password:

[Email protected] ~]#/usr/local/mysql/bin/mysql-urootwelcome to the MySQL monitor.  Commands End With; or \g.your MySQL connection ID is 1Server version:5.1.40-log mysql Community Server (GPL) Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>

If you exit, simply enter quit or exit. The attentive reader may find that the Amin in the previous command, using an absolute path, is inconvenient, but simply entering a "MySQL" command alone is not possible, because "/usr/local/mysql/bin" is not in the PATH environment variable. How do I add it to the environment variable path? Previously Amin introduced:

[Email protected] ~]# path= $PATH:/usr/local/mysql/bin

This will do, but it will fail after restarting Linux, so it needs to be loaded on the boot:

[[email protected] ~]# echo "path= $PATH:/usr/local/mysql/bin" >>/etc/profile[[email protected] ~]# source/etc/ Profile[[email protected] ~]# mysql-urootwelcome to the MySQL monitor.  Commands End With; or \g.your MySQL connection ID is 2Server version:5.1.40-log mysql Community Server (GPL) Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>

Amin again to explain the meaning of the previous command-u, it is used to specify the user to log in, there can be spaces, or no space, the root user is the MySQL with the administrator account, the default is no password, then how to set the root user password? Follow these steps:

[Email protected] ~]# mysqladmin-uroot password ' yourpassword '

This will set the ' root ' account password, may wish to use the above command to log on to try:

[[email protected] ~]# Mysql-urooterror 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)

Error, this is to prompt us, root account is required password login.

[Email protected] ~]# mysql-uroot-p ' YourPassword ' Welcome to the MySQL monitor.  Commands End With; or \g.your MySQL connection ID is 7Server version:5.1.40-log mysql Community Server (GPL) Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>

Need to add a-p option, it can be followed directly with the password, there can be no space, but the password is best enclosed in single quotation marks, not included, but if there are special characters in the password will be problematic, so it is best to enclose it. Of course, after-P is also can not add password, but with the user interaction way, let us enter the password:

[Email protected] ~]# mysql-uroot-penter Password:

Connecting to a database

Just talked about using it to connect to the mysql -u root -p database, but this is only the local database "localhost" connected, but there are many times to connect to a network on a host of MySQL.

[Email protected] ~]# mysql-uroot-p-h192.168.137.10-p3306enter Password:

The back of the-p (uppercase) is used to specify the remote host MySQL binding port, the default is 3306,-H is used to specify the remote host IP.

Some basic MySQL operation commands

1. Querying the current library

Mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema (mysql) |              | Test               |+--------------------+3 rows in Set (0.06 sec)

MySQL command, you need a semicolon at the end.

2. Querying a table for a library

First you need to switch to a library:

mysql> Use mysql;database changed

Then list the tables:

Mysql> Show tables;+---------------------------+| Tables_in_mysql           |+---------------------------+| columns_priv              | | db                        | | event                     | | Func                      | | general_ Log               | | help_category             | | Help_keyword              | | help_relation             | | help_topic                | | host                      | | ndb_binlog_ Index          | | plugin                    | | proc                      | | Procs_priv                | | servers                   | | Slow_log                  | | Tables_priv               | | Time_zone                 | | Time_zone_leap_second     | | time_zone_name            | | time_zone_transition      | | time_zone_ Transition_type | | User                      |+---------------------------+23 rows in Set (0.06 sec)

3. View all fields of a table

Mysql> desc slow_log;+----------------+------------------+------+-----+-------------------+------------------ -----------+| Field | Type | Null | Key | Default | Extra |+----------------+------------------+------+-----+-------------------+----------------------- ------+| start_time | Timestamp |     NO | | Current_timestamp | On Update Current_timestamp | | User_host | Mediumtext |     NO | |                             NULL | || Query_time | Time |     NO | |                             NULL | || Lock_time | Time |     NO | |                             NULL | || rows_sent | Int (11) |     NO | |                             NULL | || rows_examined | Int (11) |     NO | |                             NULL | || db | VARCHAR (512) |     NO | |      NULL |                       || last_insert_id | Int (11) |     NO | |                             NULL | || insert_id | Int (11) |     NO | |                             NULL | || server_id | Int (Ten) unsigned |     NO | |                             NULL | || Sql_text | Mediumtext |     NO | |                             NULL | |+----------------+------------------+------+-----+-------------------+-----------------------------+11 rows in Set (0.04 sec)

You can also use 21 commands to show more detail than this, and you can list all the statement statements:

Mysql> Show CREATE TABLE slow_log\g;*************************** 1. Row ***************************       table:slow_logcreate table:create Table ' slow_log ' (  ' start_time ' timestamp Not null DEFAULT current_timestamp on Updatecurrent_timestamp,  ' user_host ' mediumtext not NULL,  ' Query_time ' Time NOT NULL,  ' lock_time ' time is not null,  ' rows_sent ' int (one) not null,  ' rows_examined ' int (one) not null,
   
     ' db ' varchar (+) NOT NULL,  ' last_insert_id ' int (one) not null,  ' insert_id ' int (one) not null,  ' server_id '  Int (ten) unsigned not NULL,  ' sql_text ' mediumtext not null) engine=csv DEFAULT charset=utf8 comment= ' Slow log ' 1 row in Set (0.01 sec)
   

4. See which user is currently

Mysql> Select User (); +----------------+| User ()         |+----------------+| [Email protected] |+----------------+1 row in Set (0.00 sec)

5. View the currently used database

Mysql> Select Database (); +------------+| Database () |+------------+| MySQL      |+------------+1 row in Set (0.01 sec)

6. Create a new library

mysql> CREATE DATABASE db1; Query OK, 1 row affected (0.05 sec)

7. Create a new table

mysql> use db1;database changedmysql> create table T1 (' id ' int (4), ' name ' char (40)); Query OK, 0 rows affected (0.02 sec)

Note that the field names need to be enclosed in anti-quotes.

8. View the current database version

Mysql> select version (); +------------+| Version ()  |+------------+| 5.1.40-log |+------------+1 row in Set (0.01 sec)

9. View current MySQL status

Mysql> Show status;+-----------------------------------+----------+| Variable_name                     | Value    |+-----------------------------------+----------+| Aborted_clients                   | 0        | | Aborted_connects                  | 5        | | Binlog_cache_disk_use             | 0        | | Binlog_cache_use                  | 0        | | bytes_received                    | 303 |      | Bytes_sent                        | 7001     |

Because the content is too long, Amin not all listed, if you are interested in the online search for information on the meaning of each line.

10. View MySQL Parameters

Mysql> Show variables;+-----------------------------------------+---------------------+| Variable_name                           | Value               |+-----------------------------------------+---------------------+| auto_increment_increment                | 1                   | | | Auto_increment_offset                   | 1                   | | | autocommit                              | On                  | | automatic_sp_privileges                 | On | | | back_log | | |                  basedir                                 |/usr/local/mysql/   |

Limited to space, Amin omitted a number of parameters are not shown, many of which can be defined in the/ETC/MY.CNF, and some parameters can be edited online.

11. Modify MySQL Parameters

Mysql> Show variables like ' max_connect% '; +--------------------+-------+| Variable_name      | Value |+--------------------+-------+| max_connect_errors | |    | | max_connections    | 151   |+--------------------+-------+2 rows in Set (0.00 sec) mysql> Set global Max_ connect_errors = 1000; Query OK, 0 rows affected (0.01 sec) mysql> Show variables like ' max_connect_errors '; +--------------------+-------+| Variable_name      | Value |+--------------------+-------+| max_connect_errors |  |+--------------------+-------+1 row in Set (0.01 sec)

On the MySQL command line, "%" is similar to the Shell * , which represents a universal match. Some parameters can be modified temporarily using "set global", but after restarting the MYSQLD service, it will become original, so it needs to be defined in the configuration file my.cnf for the permanent effect.

12. View the queue for the current MySQL server

This is most frequently used in daily management work, as it allows you to see what MySQL is doing and find out if there is a lock table:

Mysql> Show processlist;+----+------+-----------+------+---------+------+-------+------------------+| Id | User | Host      | db   | Command | Time | State | Info             |+----+------+-----------+------+---------+------+-------+------------------+| | root | localhost | db1  | Query   |    0 | NULL  | show processlist |+----+------+-----------+------+---------+------+-------+------------------+1 row in Set (0.01 sec)

13. Create a regular user and authorize

Mysql> Grant All on * * to User1 identified by ' 123456 '; Query OK, 0 rows affected (0.01 sec)

All represents all permissions (read, write, query, delete, and so on), preceded by the representation of all the *.* * databases, followed by the * representation of all tables, identified by followed by a password, enclosed in single quotation marks. The User1 here refers to the User1 on localhost, if it is authorized for a user on another machine on the network:

Mysql> Grant all on db1.* to ' user2 ' @ ' 10.0.2.100 ' identified by ' 111222 '; Query OK, 0 rows affected (0.01 sec)

There is an @ between the user and the host's IP, and the host IP can be replaced with%, representing all hosts, for example:

Mysql> Grant all on db1.* to ' User3 ' @ ' percent ' identified by ' 231222 '; Query OK, 0 rows Affected (0.00 sec)

Some of the commonly used SQL

1. Query statements

Mysql> Select COUNT (*) from mysql.user;+----------+| COUNT (*) |+----------+|        8 |+----------+1 row in Set (0.00 sec)

Mysql.user represents the user table of the MySQL library, and COUNT (*) indicates how many rows are in the table.

Mysql> select * from Mysql.db;

This is used to represent all the data in the DB table that queries the MySQL library, or you can query a single field or multiple fields:

Mysql> Select db from Mysql.db;mysql> select Db,user from  mysql.db;

Similarly, you can use the universal match "%" in a query statement

Mysql> SELECT * from mysql.db where host like ' 10.0.% ';

2. Insert a row

mysql> INSERT INTO DB1.T1 values (1, ' abc '); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM db1.t1;+------+------+| ID   | name |+------+------+|    1 | ABC  |+------+------+1 row in Set (0.00 sec)

3. Change a row of a table

mysql> Update db1.t1 set name= ' AAA ' where id=1; Query OK, 1 row affected (0.01 sec) Rows matched:1  changed:1  warnings:0mysql> SELECT * FROM db1.t1;+------+-- ----+| ID   | name |+------+------+|    1 | AAA  |+------+------+1 row in Set (0.00 sec)

4. Clear the table data

mysql> truncate TABLE db1.t1; Query OK, 0 rows affected (0.01 sec) mysql> Select COUNT (*) from db1.t1;+----------+| COUNT (*) |+----------+|        0 |+----------+1 row in Set (0.00 sec)

5. Delete a table

mysql> drop table db1.t1; Query OK, 0 rows Affected (0.00 sec)

6. Deleting a database

mysql> drop Database db1; Query OK, 0 rows affected (0.02 sec)

Backup and recovery of MySQL database

Backup:

[[email protected] ~]# mysqldump  -uroot-p ' yourpassword ' MySQL >/tmp/mysql.sql

Using the mysqldump command to back up the database, the-U and-P two options are used in the same way as the previous MySQL, while the following "MySQL" refers to the library name, which is then redirected to a text document. After you have finished backing up, you can view the contents of the/tmp/mysql.sql file.

Recovery and backup are just the opposite:

[[email protected] ~]# mysql-uroot-p ' yourpassword ' MySQL </tmp/mysql.sql

Simple operation of MySQL under Linux