MySQL database installation and basic operations

One-click installation script:

Cat mysql_install.sh

#!/bin.bashgroupadd mysqluseradd -s /sbin/nologin -g mysql -m mysqlcd  /usr/local/wget http://mysql.com//downloads/mysql-5.6/mysql-5.6.32-linux-glibc2.5-x86_64.tar.gztar - zxvf mysql-5.6.32-linux-glibc2.5-x86_64.tar.gzmv mysql-5.6.32-linux-glibc2.5-x86_64 mysql/bin/ Cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnfmkdir -p /usr/local/mysql/data       #data  filechown -r mysql.mysql /usr/local/mysql//usr/local/ mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data -- user=mysql/bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqldchmod +x / etc/init.d/mysqld/etc/init.d/mysqld startchkconfig --add mysqldchkconfig mysqld  onecho  ' export path=/usr/local/mysql/bin/: $PATH '  >>/etc/profilesource /etc/profile

Change the password of the MySQL database root

Mysqladmin-uroot-p "OldPassword" password "NewPassword"

or login modifications

Set password for [email protected] = password (' 123456 ');

Log in to MySQL modify:

Use MySQL;

Update user set Password=password ("123456") where user= "root";

Flush privileges;

MySQL joins the environment variable in path

[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

Connected to the database, by using Mysql-u root-p can connect to the database, but this is only the local database "localhost" connection, but there are many times to connect to a network on a host of MySQL.

[Email protected] ~]#

Mysql-uroot-p-h192.168.137.10-p3306

Enter 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 |

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

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 | |

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

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_log

Create table:create Table ' Slow_log ' (

' Start_time ' timestamp not NULL DEFAULT current_timestamp on UPDATE

Current_timestamp,

' User_host ' Mediumtext not NULL,

' Query_time ' time not NULL,

' Lock_time ' time not NULL,

' Rows_sent ' int (one) is not NULL,

' rows_examined ' int (one) is not NULL,

' DB ' varchar (+) not NULL,

' last_insert_id ' int (one) is not NULL,

' insert_id ' int (one) is 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 changed

mysql> 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 |

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 | 50 |

| Basedir | /usr/local/mysql/|

11. Modify MySQL Parameters

Mysql> Show variables like ' max_connect% ';

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

| variable_name | Value |

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

| max_connect_errors | 10 |

| 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 | 1000 |

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

1 row in Set (0.01 sec)

On the MySQL command line, "%" is similar to the * under the shell, indicating 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 |

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

| 13 | 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), * * For all databases, followed by * for all tables, identified by followed by passwords, enclosed in single quotes. 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 privileges on * * to ' wenlong ' @ '% ' identified by ' 123456 ' with GRANT OPTION;

Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;

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:0

Mysql> 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

MySQL Official Chinese reference Manual (5.1) http://dev.mysql.com/doc/refman/5.1/zh/index.html

MySQL database installation and basic operations