Mysql database common operations and techniques (DBA prerequisite knowledge) _mysql

The MySQL database is one of the most common and commonly used databases of DBAs, and the extensive application of MySQL has also allowed more people to join in learning it. Here are some of the most common and commonly used experiences and techniques shared by the MySQL DBA, which is summarized by old MySQL DBAs.

A, MySQL database backup

Using the MySQL database, the most common and most important is the database backup, so we first introduced the database backup. Database backup, and very formal database backup method, with other database servers have the same concept, but have you ever thought that MySQL will have a simpler use of the file directory backup method, but also quick to have good (this method has not been the official official document verification, Let's call it an experiment.

Objective: to backup a MySQL database in the Hosta host Testa 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 subdirectory C:mysqldatatesta;

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

6, refresh HostB phpmyadmin look at the list of databases, we see that Testa has appeared, and make query modifications and other operations are normal, backup restore success.

Test Conclusion: MySQL's database can be saved, backed up, and recovered by file, as long as the appropriate file directory is restored and no other tools are available for backup.

Second, the connection MySQL

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

1, connected to the MySQL on the computer.

First in the Open DOS window, and then into 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 direct return can enter into MySQL, MySQL's prompt is: MySQL.

2, connect to the remote host MySQL. Assume the IP of the remote host is: 110.110.110.110, username is root, password is abcd123. Type the following command:

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

3, exit MySQL command: Exit (enter).

Third, the Operation skill

1, if you hit the command, when you find that you forgot to add a semicolon, you do not need to repeat the command, as long as a semicolon to enter on it. That is, you can divide a complete command into several lines and then complete it with a semicolon as the closing sign.

2, you can use the cursor up and down keys to pull up the previous command. But I used to use a MySQL older version does not support. I'm using a mysql-3.23.27-beta-win.

Four, display the command

1. Display Database list:

show databases;

Only two databases were initially: MySQL and test. MySQL Library is very important it has MySQL system information, we change the password and new users, is actually using this library to operate.

2, display the data table in the library:

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

Show tables;

3, display the structure of the data table:

describe table name;

4, build the library:

Create database library name;

5, the establishment of the table:

Use library name;

CREATE TABLE table name (field set list);

6, delete the database and delete the table:

drop Database library name;

The drop table table name;

7. Empty the record of the table:

Delete from table name;

8, display the records in the table:

SELECT * from table name;

Example of how far and table-building and inserting data

Drop database if exists school; Delete if there is school

Create Database School; Build 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 (a) NOT NULL,

Address varchar default ' Shenzhen ',

Year Date

); Build Table End

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 table (1), set the ID to a number field of length 3: int; (2) and let it automatically add one for each record: Auto_increment cannot be empty: NOT null and let him be the primary field primary key; (3) Set name to 10 character field (4) Set the address to a character field of length 50 and the default value is Shenzhen. What is the difference between varchar and char, only to wait for later articles; (5) Set year as Date field.

If you type the above command at the MySQL prompt, it's not easy to debug. You can write the above command in a text file as if it were school.sql, then copy it to c:\\ and enter the directory \\mysql\\bin in DOS, and then type the following command:

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

If successful, there is no display on a single line, and if there is an error, there is a hint. (The above command has been debugged and you can use it only if you remove the comment.)

Six, modify the password

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

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

Mysqladmin-uroot-password AB12

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

2, and then the root of the password changed to djg345.

MYSQLADMIN-UROOT-PAB12 Password djg345

seven , increase Add new users. (Note: Unlike the above, the following are the commands in the MySQL environment, followed by a semicolon as the 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 connect the root user to MySQL, and then type the following command:

Grant Select,insert,update,

Delete on *.* to test1@\ "%\" identified by \ "Abc\";

But for example 1 the increased user is very dangerous and you want someone who knows Test1 's password so that he can log on to your MySQL database on any computer on the Internet and can do whatever it wants with your data, as shown in Example 2.

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

Grant Select,insert,update,

Delete on mydb.* to test2@localhost identified by \ "Abc\";

If you do not want to test2 the password, you can make another command to eliminate the password.

Grant Select,insert,update,delete on MyDB

. * to test2@localhost identified by \ "\";

The above is some MySQL database common operation and skill.