Debian system installation and configuration of MySQL database and Basic usage

It is very convenient to install the MySQL server in Debian. you can use the apt-get command. Debian :~ # Apt-getinstallmysql-servermysql-clientmysql-server is a server program, and mysql-client is a client program. We can manage servers through client programs, or maintain servers through some open-source GUI programs.

It is very convenient to install the MySQL server in Debian. you can use the apt-get command.

Debian :~ # Apt-get install Mysql-Server Mysql-Client

Mysql-server is a server program, and mysql-client is a client program. We can use client programs to manage servers, or some open-source GUI programs to maintain servers, suchPhpmyadminAnd mysqlcc. RecommendedPhpmyadminThis B/S management program allows you to conveniently and efficiently manage databases on the network through a browser.

For MySQL database management operations, refer to the MySQL study notes on this site.

3. log on to MySQL

The command used to log on to MySQL is mysql. the syntax of mysql is as follows:
Mysql [-u username] [-h host] [-p [password] [dbname]
Username and password are the username and password of MySQL respectively. the initial management account of mysql is root, and there is no password. note: This root user is not a Linux system user. The default MySQL User is root. because there is no password at first, you only need to type mysql for the first time.
[Root @ test1 local] # mysql
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 1 to server version: 4.0.16-standard
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
Mysql>
The prompt "mysql>" appears. Congratulations! the installation is successful!
The logon format after the password is added is as follows:

Enter password: (Enter the password)
-U is followed by the user name.-p requires the password. press enter and enter the password at the enter password.

Note: This mysql file is located in the/usr/bin directory. it is not a file with the startup file/etc/init. d/mysql described later.

　　4. several important MySQL directories

After MySQL is installed, its database files, configuration files, and command files are not installed in the default directory of SQLServer. it is very important to understand these directories, especially for Linux beginners, because the directory structure of Linux itself is complicated, if you do not know the installation directory of MySQL, you will not be able to learn it in depth.

The following describes these directories.

1. Database Directory
/Var/lib/mysql/

2. configuration file
/Usr/share/mysql (mysql. server command and configuration file)

3. related commands
/Usr/bin (commands such as mysqladmin mysqldump)

4. start the script
/Etc/rc. d/init. d/(directory for starting the script file mysql)

5. change the logon password

MySQL does not have a password by default. it is self-evident that the password is added after installation.

1. commands
Usr/bin/mysqladmin-u root password 'new-password'
Format: mysqladmin-u username-p old password new password

2. example
Example 1: Add a 123456 password to the root user.
Type the following command:
[Root @ test1 local] #/usr/bin/mysqladmin-u root password123456
Note: Because the root account does not have a password at the beginning, the old-p password can be omitted.

3. test whether the modification is successful
1) login without a password
[Root @ test1 local] # mysql
ERROR 1045: Access denied for user: 'root @ localhost' (Usingpassword: NO)
An error is displayed, indicating that the password has been modified.
2) log on with the modified password
[Root @ test1 local] # mysql-u root-p
Enter password: (Enter the password 123456 after modification)
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 4 to server version: 4.0.16-standard
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
Mysql>
Successful!
You can use the mysqladmin command to change the password or the database to change the password.

　　6. start and stop

1. start
After MySQL is installed, run the following command to start mysql in the/etc/init. d Directory.
[Root @ test1 init. d] #/etc/init. d/mysql start

2. stop
/Usr/bin/mysqladmin-u root-p shutdown

3. automatic start
1) Check whether mysql is in the auto start list
[Root @ test1 local] #/sbin/chkconfig? List

PS:The command chkconfig is not used by default. you need to install it on your own. The installation in debian is simple and has apt source.
2) add MySQL to the startup service group of your system.
[Root @ test1 local] #/sbin/chkconfig? Add mysql
3) delete MySQL from the startup service group.
[Root @ test1 local] #/sbin/chkconfig? Del mysql

　7. change the MySQL Directory

The default data file storage directory of MySQL is/var/lib/mysql. To move the directory to/home/data, perform the following steps:

1. create a data directory under the home directory
Cd/home
Mkdir data

2. stop the MySQL service process:
Mysqladmin-u root-p shutdown

3. move the entire/var/lib/mysql directory to/home/data
Mv/var/lib/mysql/home/data/
In this way, the MySQL data file is moved to/home/data/mysql.

4. find the my. cnf configuration file.
If my. for the cnf configuration file, go to/usr/share/mysql/and find *. copy one of the cnf files to/etc/and change it to my. cnf. The command is as follows:
[Root @ test1 mysql] # cp/usr/share/mysql/my-medium.cnf/etc/my. cnf

5. edit the MySQL configuration file/etc/my. cnf.
To ensure that MySQL works properly, you must specify the location where the mysql. sock file is generated. Change socket =/var/lib/mysql. sock to/home/mysql. sock. The procedure is as follows:
Vi my. cnf (use the vi tool to edit the my. cnf file and find the following data to modify)
# The MySQL server
[Mysqld]
Port = 3306
# Socket =/var/lib/mysql. sock)
Socket =/home/data/mysql. sock (add this line)

6. modify the MySQL startup script/etc/rc. d/init. d/mysql
Finally, you need to modify the MySQL startup script/etc/rc. d/init. d/mysql: change the path on the right of datadir =/var/lib/mysql to your actual storage path: home/data/mysql.
[Root @ test1 etc] # vi/etc/rc. d/init. d/mysql
# Datadir =/var/lib/mysql (comment this row)
Datadir =/home/data/mysql (add this row)

7. restart the MySQL service.
/Etc/rc. d/init. d/mysql start
Or use the reboot command to restart Linux.
If it works properly, it will succeed. otherwise, check again against the previous seven steps.

　　VIII. common MySQL operations

Note: Each command in MySQL must end with a semicolon.

1. display the database
Mysql> show databases;
+ ---------- +
| Database |
+ ---------- +
| Mysql |
| Test |
+ ---------- +
2 rows in set (0.04 sec)
Mysql has just been installed with two databases: mysql and test. The mysql database is very important. it contains MySQL system information. we change the password and add new users. In fact, we use the relevant tables in this database for operations.

2. display tables in the database
Mysql> use mysql; (open the database. to operate on each database, open the database, similar to foxpro)
Database changed

Mysql> show tables;
+ ----------------- +
| Tables_in_mysql |
+ ----------------- +
| Columns_priv |
| Db |
| Func |
| Host |
| Tables_priv |
| User |
+ ----------------- +
6 rows in set (0.01 sec)

3. display the data table structure:
Describe table name;

4. display the records in the table:
Select * from table name;
For example, the user table records in the mysql database are displayed. All users who can operate on MySQL users are in this table.
Select * from user;

5. database creation:
Create database name;
For example, create a database named aaa
Mysql> create databases aaa;

6. create a table:
Use database name;
Create table name (field setting list );
For example, if you create a table name in the newly created aaa database, the table has four fields: id (serial number, auto-increment), xm (name), xb (gender), and csny (date of birth ).
Use aaa;
Mysql> create table name (id int (3) auto_increment not nullprimary key, xm char (8), xb char (2), csny date );
You can use the describe command to view the created table structure.
Mysql> describe name;

+ ------- + --------- + ------ + ----- + --------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + --------- + ------ + ----- + --------- + ---------------- +
| Id | int (3) | PRI | NULL | auto_increment |
| Xm | char (8) | YES | NULL |
| Xb | char (2) | YES | NULL |
| Csny | date | YES | NULL |
+ ------- + --------- + ------ + ----- + --------- + ---------------- +

7. add Records
For example, add several related records.
Mysql> insert into name values ('', 'Zhang San', 'mal', '2017-10-01 ');
Mysql> insert into name values ('', 'baiyun ', 'female', '2017-05-20 ');
The select command can be used to verify the result.
Mysql> select * from name;
+ ---- + ------ + ------------ +
| Id | xm | xb | csny |
+ ---- + ------ + ------------ +
| 1 | Zhang San | male |
| 2 | Baiyun | female | 1972-05-20 |
+ ---- + ------ + ------------ +

8. modify records
For example, change the date of birth of John
Mysql> update name set csny = '2017-01-10 'where xm = 'Zhang San ';

9. delete records
For example, delete the records of Michael Jacob.
Mysql> delete from name where xm = 'Zhang San ';

10. delete databases and tables
Drop database name;
Drop table name;

　　9. add MySQL Users

Format: grant select on database. * to username @ login host identified by "password"
Example 1: add a user user_1 with a password of 123 so that he can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MySQL, and then type the following command:

Mysql> grant select, insert, update, delete on *. * touser_1 @ "%" Identified by "123 ";
In example 1, the added user is very dangerous. if you know the user_1 password, then he can log on to your MySQL database on any computer on the internet and do whatever he wants. for the solution, see example 2.

Example 2: Add a user_2 password of 123 so that the user can only log on to localhost, you can also query, insert, modify, and delete the database aaa (localhost refers to the local host, that is, the host where the MySQL database is located), so that the user knows the password of user_2, he cannot directly access the database from the Internet, and can only operate the aaa database through the MYSQL host.

Mysql> grant select, insert, update, delete on aaa. * touser_2 @ localhost identified by "123 ";

If a new user cannot log on to MySQL, run the following command during logon:

Mysql-u user_1-p-h 192.168.113.50 (-h is followed by the IP address of the host to be logged on)

We know that in ms SQL server or access,
To query the first 10 records, useTop10,
HoweverMysqlThis statement is not supported. it uses limit 10.

We can useMySQLSELECT supports a clause -- LIMIT -- to complete this function.
LIMIT can be implementedTopN query can also be used to query records from M to N (a segment). the specific syntax is as follows:
SELECT * FROM MYTABLE
ORDER BY AFIELD
LIMIT offset, recnum
Here, offset starts from the number of records (M + 1) and recnum is the number of returned Records. Example:
Select * from mytable
Order by afield
Limit 2, 5
That is, five records starting from 3rd records.

　　10. backup and recovery

1. backup

For example, back up the aaa library created in the previous example to the back_aaa file.

[Root @ test1 root] # cd/home/data/mysql (go to the Database Directory, this example Library has been transferred from val/lib/mysql to/home/data/mysql, see Section 7 above)
[Root @ test1 mysql] # mysqldump-u root-p -- opt aaa> back_aaa

2. recovery

[Root @ test mysql] # mysql-u root-p ccc <back_aaa

11. FAQs:

1. "Can't connect to local MySQL server through socket '/var/lib/mysql. sock'" error

Solution:

This is because the mysql daemon is not started. just execute service mysqld start.

2.

If you want to connect to your mysql instance, this error occurs:
ERROR 1130: Host '192. 13.92.66 'is not allowed to connect to thisMySQL server
Follow these steps:

Login as: root // log on to the system
Root@tqwm.cn's password: // enter the password
Last login: Tue Apr 15 14:06:54 2008 from 210.13.92.66
[Root @ myserver ~] #/Usr/local/mysql/bin/mysql-uroot-hlocalhost-p // log on to mysql
Enter password: // Enter the password of the mysql User
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 431
Server version: 5.0.58-enterprise-gpl-log Source

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer. // message indicating successful logon

Mysql> grant all privileges on *. * TO 'root' @ '%' IDENTIFIEDBY 'rootpasswd' with grant option; /// enter this command and press enter to allow all hosts that use the root user and enter the rootpasswd password to log on to the mysql Server. If '%' is changed to '10. 1.1.1 'only hosts of 10.1.1.1 can log on.
Query OK, 0 rows affected (0.07 sec)

Mysql> \ q // exit mysql
Bye
[Root @ myserver ~] #