Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the common operations and skills of MySQL database, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

MySQL database can be said to be one of the most common and commonly used databases for DBA. The wide application of MySQL also makes more people join in learning it. Here are some of the most common and commonly used experiences and techniques of MySQL database summarized by the old MySQL DBA to share with you!

I. backup of MySQL database

Using MySQL database, the most common and most important thing is database backup, so let's introduce database backup first. Database backup, and a very formal database backup method, and other database servers have the same concept, but have not thought that MySQL will be easier to use file directory backup method, and soon have good (this method has not been verified by official documents, we temporarily call it an experiment).

Objective: to back up a mysql database TestA in the hostA host and restore it to the host B machine.

Test environment:

Operating system: WinNT4.0,Mysql3.22.34,phpMyAdmin 2.1.0

Install mysql database and establish TestA database in hostA

Host B machine installs mysql database, no TestA database.

Methods and steps:

1. Start phpMyAdmin to view the list of databases in HostA and HostB. There is no TestA database in HostB.

2. Find the installation directory of mysql in HostA, and find the database directory data

3. In my experimental environment, this directory is C:mysqldata

4. Find the subdirectory C:mysqldataTestA of the corresponding database name

5. Paste and copy to the Data directory of HostB. The HostA is the same as the files in the HostB Mysql data directory.

6. Refresh the phpMyAdmin of HostB and take a look at the database list. We can see that TestA has appeared and the operations such as query modification are normal, and the backup recovery is successful.

Test conclusion: the database of Mysql can be saved, backed up and restored in the form of files, as long as the corresponding file directory is restored, and there is no need to use other tools to back up.

Second, connect to MySQL

Format: mysql-h host address-u user name-p user password

1. Connect to the MYSQL on this machine.

First of all, open the DOS window, then enter the directory mysqlbin, and then type the command mysql-uroot-p. Enter prompts you to enter the password. If you have just installed MYSQL, the superuser root has no password, so you can enter the MYSQL directly by entering the enter. The prompt for MYSQL is: mysql ".

2. Connect to the MYSQL on the remote host. Suppose the IP of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Type the following command:

Mysql-h210.110.110.110-uroot-pabcd123 (Note: U and root can not add spaces, others are the same)

3. Exit the MYSQL command: exit (enter).

Third, operation skills

1. If you enter the order and find that you forgot to add the semicolon, you don't have to type the command again, just hit the semicolon and enter. In other words, you can type a complete command into several lines and finish it with a semicolon.

You can use the cursor up and down keys to call up the previous command. But an old version of MySQL that I used before doesn't support it. I am using mysql-3.23.27-beta-win now.

Fourth, display commands

1. Display a list of databases:

Show databases

At the beginning, there were only two databases: mysql and test. The MySQL library is very important. it contains the system information of MYSQL. We actually use this library to change passwords and add users.

2. Display the data table in the library:

Use mysql; / / Open the library, those who have studied FOXBASE must be familiar with it.

Show tables

3. Display the structure of the data table:

Describe table name

4. Build the database:

Create database library name

5. Create a table:

Use library name

Create table table name (list of field settings)

6. Delete the library and the table:

Drop database library name

Drop table table name

7. Clear the records in the table:

Delete from table name

8. Display the records in the table:

Select * from table name

5. An example of building a database and table and inserting data

Drop database if exists school; / / delete if SCHOOL exists

Create database school; / / build library SCHOOL

Use school; / / Open the library SCHOOL

Create table teacher / / create table TEACHER

(

Id int (3) auto_increment not null primary key

Name char (10) not null

Address varchar (50) default 'Shenzhen'

Year date

); / / end of table creation

/ / the following are insert fields

Insert into teacher values (', 'glchengang',' Shenzhen No.1 Middle School', '1976-10-10')

Insert into teacher values (', 'jack',' Shenzhen No.1 Middle School', '1975-12-23')

Note: in the establishment of the table (1) set ID to the length of 3 of the number field: int; (2) and let it automatically add one for each record: auto_increment can not be empty: not null and let him become the main field primary key; (3) set NAME to the length of 10 character field; (4) set ADDRESS to the length of 50 character field, and the default value is Shenzhen. What's the difference between varchar and char? we'll have to wait for a later article. (5) set YEAR to the date field.

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

Mysql-uroot-p password "c:\ school.sql"

If successful, leave a line without any display; if there is an error, there will be a prompt. The above command has been debugged, you just need to remove the comment from / / and you can use it.

VI. Change the password

Format: mysqladmin-u username-p old password password new password

1. Add a password ab12 to root. First enter the directory mysqlbin under 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. Change the password of root to djg345.

Mysqladmin-uroot-pab12 password djg345

Seventh, add new users. (note: unlike the above, the following is a command in the MySQL environment, so it is 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's test1 password to abc, so that he can log in on any host and have the authority to query, insert, modify and delete all databases. First use the root user to connect to MySQL, and then type the following command:

Grant select,insert,update

Delete on *. * to test1@ "%" Identified by "abc"

But the increase in the number of users in example 1 is very dangerous, if you want someone who knows the password of test1, then he can log in to your MySQL database on any computer on internet and can do whatever he or she wants with your data.

Example 2, add a user's test2 password to abc, so that he can only log in on localhost, and can query, insert, modify and delete the database mydb (localhost refers to the local host, that is, the host where the MySQL database is located), so that even if the user knows the test2 password, he can not access the database directly from the internet, but only through the web page on the MySQL host.

Grant select,insert,update

Delete on mydb.* to test2@localhost identified by "abc"

If you don't want test2 to have a password, you can issue another command to eliminate the password.

Grant select,insert,update,delete on mydb

. * to test2@localhost identified by ""

After reading the above, do you have any further understanding of the common operations and skills of MySQL database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.