MySQL database common operations and tips

MySQL database is one of the most common and common database of DBAs, MySQL is widely used, but also more people to join the ranks of learning it. Here are some of the most common and frequently used experiences and tips from the MySQL database that the old MySQL DBA summarizes and share to everyone!

One, backup of MySQL database

Using MySQL database, the most common and most important is the database backup, so we first introduce the database backup. Database backup, but also a very formal database backup method, and other database server has the same concept, but did not think, MySQL will have a more concise use of the file directory backup method, and quickly good (this method has not been formally verified by official documents, We call it a trial.

Purpose: back up a MySQL database testa in the Hosta host and revert to the HOSTB machine.

Test environment:

operating System : Winnt4.0,mysql3.22.34,phpmyadmin 2.1.0;
Install MySQL database in hosta and establish Testa database;
HOSTB machine installed MySQL database, no testa database.

Method steps:

1. Start phpMyAdmin to see the list of databases in Hosta and HostB, there is no Testa database in HostB;

2, find the hosta MySQL installation directory, and find the database directory data;

3, in my test environment, this directory is c:mysqldata;

4, find the corresponding database name sub-directory C:mysqldatatesta;

5, paste the copy to the HOSTB data directory, is hosta with the HostB MySQL directory of the same files;

6, refresh HostB phpmyadmin look at the database list, we see Testa has appeared, and make the query modification, and so on, the backup recovery is successful.

Test Conclusion: The MySQL database can be saved, backed up, and restored as a file, as long as the file directory is restored, without the need for other tools to back it up.

Second, connect MySQL

Format: mysql-h host address-u user name-P user Password

1. Connect to MySQL on this machine.

First open the DOS window, and then enter the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so directly enter into MySQL, The prompt for MySQL is: MySQL.

2. Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:

mysql-h110.110.110.110-uroot-pabcd123 (Note: U and root can be used without spaces, and others are the same)

3. Exit MySQL command: Exit (enter).

Three, the Operation skill

1, if you hit the command, enter after the discovery forgot to add a semicolon, you do not have to re-play the command, as long as a semicolon to enter the return on it. This means that you can break a complete command into a few lines and finish it with a semicolon as the end sign.

2. You can use the cursor up and down keys to recall the previous command. But previously I used an old version of MySQL that was not supported. I'm using Mysql-3.23.27-beta-win now.

Iv. Display of commands

1. Display the database list:

show databases;

Just started with two databases: MySQL and test. MySQL Library is very important it has the MySQL system information, we change the password and the new user, is actually using this library to operate.

2. Display the data table in the library:

Use MySQL; Open the library, learned foxbase must not be unfamiliar.

Show tables;

3, display the structure of the data table:

describe table name;

4, build the library:

Create database name;

5, build the table:

Use library name;

CREATE TABLE table name (field settings list);

6. Deleting the library and deleting the table:

drop database name;

drop table name;

7. Empty the records in the table:

Delete from table name;

8. Display the records in the table:

SELECT * from table name;

How far build and build tables and instances of inserting data

Drop database if exists school; Delete if school is present

Create Database School; Building a library School

Use school; Open Library School

CREATE TABLE teacher//Create tables Teacher

(

ID int (3) auto_increment NOT null primary key,

Name Char (TEN) is not NULL,

Address varchar (+) Default ' Shenzhen ',

Year Date

); End of Build table

The following is the Insert field

Insert into teacher values (' ', ' Glchengang ', ' Shenzhen One ', ' 1976-10-10 ');

Insert into teacher values (' ', ' Jack ', ' Shenzhen One ', ' 1975-12-23 ');

Note: In the Build table (1) Set the ID to a number field of length 3: int; (2) and let it automatically add one to each record: Auto_increment is not null and makes him the main field primary key; (3) Set name to a character field of length 10 ; (4) Set address as a character field of length 50, and the default value is Shenzhen. What is the difference between varchar and char, only to wait for future articles; (5) Set year as the Date field.

It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug. You can write the above command as-is to a text file, assume School.sql, then copy to c:\\, and enter directory \\mysql\\bin in DOS, and then type the following command:

Mysql-uroot-p Password "C:\\school.sql

If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove//comment).

Six, change the password

Format: Mysqladmin-u username-P Old password password new password

1, add a password to root ab12. First enter directory Mysqlbin under DOS, and then type the following command:

Mysqladmin-uroot-password AB12

Note: Because Root does not have a password at the beginning, the-p old password can be omitted.

2, then change the root password to djg345.

MYSQLADMIN-UROOT-PAB12 Password djg345

seven , increase add a new user.

(Note: Unlike the above, the following is because it is a command in a MySQL environment, so it is followed by a semicolon as a command terminator)

Format: Grant Select on database. * To User name @ login host identified by \ "Password \"

Example 1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:

Grant Select,insert,update,

Delete on * * to [email protected]\ "%\" identified by \ "Abc\";

But example 1 increases the user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log on your MySQL database and to your data can do whatever, workaround see Example 2.

Example 2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.

Grant Select,insert,update,

Delete on mydb.* to [e-mail protected] identified by \ "Abc\";

If you do not want to test2 have a password, you can call another command to erase the password.

Grant Select,insert,update,delete on MyDB

. * to [e-mail protected] identified by \ "\";

Common practices and tricks for MySQL databases