Summary of MySQL usage under CentOS

one, MySQL installation

Install MySQL under CentOS Please open: http://www.centoscn.com/CentosServer/sql/2013/0817/1285.html

Ii. several important directories of MySQL

MySQL installation is not as if SQL Server is installed by default in a directory, its database files, configuration files and command files in different directories, it is important to understand these directories, especially for the Linux beginners, because the Linux itself directory structure is more complex, If you don't know the MySQL installation directory then you can't talk about deep learning.

Here are a few of the directories to look at.

2.1. Database Directory

/var/lib/mysql/

2.2. Configuration files

/usr/share/mysql (mysql.server command and configuration file)

2.3. Related commands

/usr/bin (Mysqladmin mysqldump and other commands)

2.4. Startup script

/etc/rc.d/init.d/(startup script file for MySQL directory)

Third, log in MySQL 3.1, connect the native MySQL

Example 1: Connect to MySQL on this computer.

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, MySQL prompt It's:mysql>.

3.2. Connect to remote MySQL

Example 2: Connect to MySQL on a 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: You and root can be used without spaces, others are the same)

3.3. Quit MySQL

Command: Exit (enter).

Iv. Modify Login Password

MySQL does not have a password by default, the importance of increasing the password is self-evident.

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

4.1. Change password for the first time

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

4.2. Change the password again

Example 2: Then change the root password to djg345.

MYSQLADMIN-UROOT-PAB12 Password djg345

V. Increase the number of users

(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 \ "\";

With a new user, if you can't log in to MySQL,

At logon, use the following command: Mysql-u user_1-p-H 192.168.113.50 (-H followed by the IP address of the host to be logged in)

Six, start and stop

6.1. Start

After the MySQL installation is complete, start the file MySQL in the/ETC/INIT.D directory and run the following command when it needs to start.

[[email protected] init.d]#/etc/init.d/mysql start

6.2. Stop

/usr/bin/mysqladmin-u root-p shutdown

6.3, automatically start 6.3.1, see if MySQL is in the auto-start list

[Email protected] local]#/sbin/chkconfig–list

6.3.2, add MySQL to your system's startup service group

[[email protected] local]#/sbin/chkconfig–add MySQL

6.3.3, remove MySQL from the boot service group.

[[email protected] local]#/sbin/chkconfig–del MySQL

Seven, change the MySQL directory

The default data file storage directory for MySQL is/var/lib/mysql.

The following steps are required if you want to move the directory to/home/data:

7.1. Set up the data directory in the home directory

Cd/home mkdir Data

7.2. Stop the MySQL service process:

Mysqladmin-u root-p shutdown

7.3. Move/var/lib/mysql Entire directory to/home/data

mv/var/lib/mysql/home/data/

This will move the MySQL data file to/home/data/mysql.

7.4. Locate the MY.CNF configuration file

If there is no MY.CNF configuration file under the/etc/directory, locate the *.cnf file under/usr/share/mysql/, and copy one of them to/etc/and rename it to MY.CNF).

The command is as follows:

[Email protected] mysql]# CP/USR/SHARE/MYSQL/MY-MEDIUM.CNF/ETC/MY.CNF

7.5. Edit the MySQL configuration file/etc/my.cnf

To ensure that MySQL works correctly, you need to indicate where the Mysql.sock file is generated.

Modify the value in the Socket=/var/lib/mysql/mysql.sock line to the right of the equals sign:/home/mysql/mysql.sock.

The operation is as follows:

VI my.cnf

(Use VI tool to edit the my.cnf file, find the following data modification)

# the MySQL server [mysqld]

Port = 3306

#socket =/var/lib/mysql/mysql.sock (original content, in order to be more secure with "#" Comment this line)

Socket =/home/data/mysql/mysql.sock (plus this line)

7.6. Modify MySQL startup script/etc/rc.d/init.d/mysql

Finally, the MySQL startup script needs to be modified/etc/rc.d/init.d/mysql, the path to the right of the equal sign in the Datadir=/var/lib/mysql line is changed to your current actual storage path: Home/data/mysql.

[Email protected] etc]# Vi/etc/rc.d/init.d/mysql

#datadir =/var/lib/mysql (Note this line)

Datadir=/home/data/mysql (plus this line)

7.7. Restart MySQL Service

/etc/rc.d/init.d/mysql start

or restart Linux with the reboot command

If the work is moving properly, otherwise check the previous 7 steps.

Viii. common operations for MySQL

Note: Each command in MySQL is followed by a semicolon;

8.1, MySQL common operation command 8.1.1, display 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.

8.1.2, display the data table in the library:

use MySQL;//Open the library, learn foxbase must not be unfamiliar with it

Show tables;

8.1.3, display the structure of the data table:

describe table name;

8.1.4, building the library:

Create database name;

8.1.5, Build table:

Use library name;

CREATE TABLE table name (field settings list);

8.1.6, deletion and deletion of tables:

drop database name;

drop table name;

8.1.7, empty the records in the table:

Delete from table name;

8.1.8, displays the records in the table:

SELECT * from table name;

8.1.9, add record

For example: Add a few related records.

mysql> INSERT into name values (' ', ' Zhang San ', ' Male ', ' 1971-10-01 ');

mysql> INSERT into name values (' ', ' white Clouds ', ' female ', ' 1972-05-20 ');

You can use the Select command to verify the results.

Mysql> select * from name;

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

| ID | XM | XB | CSNY |

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

| 1 | Zhang San | Male | 1971-10-01 |

| 2 | Baiyun | Women | 1972-05-20 |

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

8.1.10, change of record

For example: Change Zhang San's birth date to 1971-01-10

Mysql> Update name set csny= ' 1971-01-10 ' where xm= ' Zhang San ';

8.1.11, deleting Records

For example: Delete the Zhang San record.

mysql> Delete from name where xm= ' Zhang San ';

8.2. An instance of building and building tables and 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 (3) and let it automatically add one to each record: Auto_increment is not null, and makes him the main field primary Key (2) sets the name to a character field of length 10 (3) To set 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 a later article to say. (4) 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).

Ix. Modifying the structure of the database

9.1. Field Operation 9.1.1, add field

ALTER TABLE dbname Add column < field name >< field Options >

9.1.2, modifying fields

ALTER TABLE dbname change < old field name > < new field name >< options >

9.1.3, deleting fields

ALTER TABLE dbname drop column < field name >

10. Data Export

There are several ways to export data:

Using the SELECT INTO outfile "filename" statement

Using the Mysqldump utility

10.1. Use SELECT INTO outfile "filename" statement

It can be executed either under MySQL's command line or in a PHP program. Let me take the following example under the MySQL command line. When used in PHP, it is changed to the corresponding query for processing. However, when using this command, the user is required to have file permissions. If we have a library for phptest, there is a table for driver. Now you have to unload the driver into a file. Execute command:

mysql> use phptest;

Database Changed

Mysql> SELECT * from driver to outfile "A.txt";

Query OK, rows affected (0.05 sec)

The above can be done to unload the table driver from the database into the A.txt file. Note file names are quoted in single quotes. So where is this file? There is a data directory under the MySQL directory, which is where the database files are placed. Each library is in a separate subdirectory, so the Phptest directory is c:\mysql\data\phptest (note: My MySQL is installed under C:\mysql). OK, now we go in, a.txt is it. Open this file, which might be:

1 Mika Hakinnen 1

2 David Coulthard 1

3 Michael Schumacher 2

4 Rubens Barrichello 2

...

There may be a lot of records. Each field is separated by a tab (\ t). Then we can modify the directory of the output file name so that it is placed in the specified location. such as "A.txt" can be changed to "./a.txt" or "/a.txt". where "./a.txt" is placed in the C:\mysql\data directory, and the "/a.txt" file is placed in the C: \ directory. So the Select command considers the current directory to be the storage directory for the database, which is c:\mysql\data.

You can also use the Select command to specify delimited characters, escape characters, including characters, and record line separator characters between fields when unloading a file. are listed below:

Fields

TERMINATED by "\ T"

[Optionally] Enclosed by ""

Escaped by ""

LINES

TERMINATED by "\ n"

TERMINATED indicates field separation

[Optionally] Enclosed indicates what characters are included in the field, and if optionally is used, only char and Verchar are included escaped indicate what to use as escape characters when escaping lines TERMINATED Indicates what separates each row of records with the default values for the columns above, and the items are optional, and the default values are used if not selected. Modifications can be made as needed. Give an example as follows:

Mysql> SELECT * from driver to outfile "a.txt" Fields terminated by ","

Enclosed by "";

Query OK, rows affected (0.06 sec)

The results may be as follows:

"1", "Mika", "Hakinnen", "1"

"2", "David", "Coulthard", "1"

"3", "Michael", "Schumacher", "2"

"4", "Rubens", "Barrichello", "2"

...

You can see that each field is delimited with ",", and each field is included with "". Note that the row record delimiter can be a string, so you should test it yourself. However, if the output file in the specified directory if there is an error, first remove the test.

10.2. Using the Mysqldump utility

As you can see from the Select method above, the output file has only data, not a table structure. Also, only one table can be processed at a time, and it is not easy to process multiple tables. However, you can write the Select command to a SQL file (it should be easy to copy the text), and then execute it at the command line: The MySQL library name comes first in the simplest:

Mysqldump phptest > A.sql

The possible results are as follows:

# MySQL Dump 7.1

#

# Host:localhost Database:phptest

#--------------------------------------------------------

# Server Version 3.22.32-shareware-debug

#

# Table structure for table "Driver"

#

CREATE TABLE Driver (

drv_id Int (one) DEFAULT "0" not NULL auto_increment,

Drv_forename varchar (page) DEFAULT "" Not NULL,

Drv_surname varchar (+) DEFAULT "" Not NULL,

Drv_team Int (one) DEFAULT "0" not NULL,

PRIMARY KEY (drv_id)

);

#

# Dumping data for table "Driver"

#

INSERT into Driver VALUES (1, "Mika", "Hakinnen", 1);

INSERT into Driver VALUES (2, "David", "Coulthard", 1);

INSERT into Driver VALUES (3, "Michael", "Schumacher", 2);

INSERT into Driver VALUES (4, "Rubens", "Barrichello", 2);

...

If there are multiple tables, they are listed below. Can see this file is a full SQL file, if you want to import it into the other database can be through the command line way, very convenient: MySQL phptest < a.sql. If you upload data locally to the server, you can upload the file and then mount the data on the server through the command line.

If you only want to unload the build instruction, the command is as follows:

mysqldump-d phptest > A.sql

If you only want to unload the SQL command that inserts the data, and you do not need to create a table command, the command is as follows:

Mysqldump-t phptest > A.sql

So what should I do if I only want the data and I don't want any SQL commands?

mysqldump-t./Phptest Driver

Only the-t parameter is specified to unload the plain text file, which represents the directory where the data is unloaded./represents the current directory, that is, the same directory as mysqldump. If you do not specify a driver table, the data for the entire database is unloaded. Each table generates two files, one for the. sql file, which contains the build table execution. The other is a. txt file that contains only data and no SQL instructions.

For the unloaded data file, you can also specify the field delimiter, including the character, Escape field, and row record delimiter, as with the Select method. The parameters are listed below:

--fields-terminated-by= Field Delimiter

--fields-enclosed-by= field include character

--fields-optionally-enclosed-by= field include character, only on Char and Verchar fields

--fields-escaped-by= Escape character

--lines-terminated-by= Line Record delimiter

I think we should understand the meaning of these parameters. An example is as follows:

mysqldump-t./--fields-terminated-by=,--fields-enclosed-by= "Phptest driver

The output is:

"1", "Mika", "Hakinnen", "1"

"2", "David", "Coulthard", "1"

"3", "Michael", "Schumacher", "2"

"4", "Rubens", "Barrichello", "2"

...

Please note the use of characters.

10.3. Summary

The above is a way to unload text using the Select and Mysqldump utilities. The select is suitable for processing by the program, while the mysqldump is manual, provides powerful export capabilities, and can handle the entire library, or multiple tables specified in the library. You can use it at your own discretion and on demand.

There are also some methods, such as direct database file copy can be, but the mobile database system and the original system should be consistent. There is no longer a mention here.

11. Data Import

Similar to exporting, there are two ways to import:

Using the load DATA INFILE "filename" command

Using the Mysqlimport utility

Using SQL files

Since the first two processing is similar to the export process, only their inverse operation, so only a few examples of the use of commands, no longer explain, you can consult the manual.

11.1. Use the load command:

Load data infile "driver.txt" into table driver fields terminated by ","

Enclosed by "";

11.2, using the Mysqlimport utility program:

Mysqlimport--fields-terminated-by=,--fields-enclosed-by= "Phptest driver.txt

11.3. Using SQL file

You can use the SQL file exported by mysqldump to execute the MySQL library name at the command line.

The first thing to declare is that, in most cases, MySQL is required to have root permissions in MySQL, so the general user cannot change the password unless the administrator is requested.

Method One

With phpMyAdmin, this is the simplest, modify the user table of the MySQL library, but don't forget to use the password function.

Method Two

Using Mysqladmin, this is a special case of the preceding declaration.

Mysqladmin-u root-p Password mypasswd

After entering this command, you need to enter the root's original password, then the root password will be changed to MYPASSWD. Change the root of the command to your username, and you can change your own password. Of course if your mysqladmin is not connected to MySQL server, or you have no way to execute mysqladmin, then this method is invalid. And mysqladmin can't erase the password.

The following methods are used at the MySQL prompt and must have root privileges for MySQL:

Method Three

Mysql> INSERT into Mysql.user (Host,user,password)

VALUES ('% ', ' Jeffrey ', PASSWORD (' biscuit '));

Mysql> FLUSH Privileges

Specifically, this is the addition of a user named Jeffrey, with a password of biscuit. There is this example in the MySQL Chinese user's manual, so I wrote it out. Note that you want to use the password function, and then you also use the flush privileges.

Method Four

As with method Three, just use the Replace statement

Mysql> REPLACE into Mysql.user (Host,user,password)

VALUES ('% ', ' Jeffrey ', PASSWORD (' biscuit '));

Mysql> FLUSH Privileges

Method Five

Using the Set Password statement,

mysql> SET PASSWORD for [email protected] "%" = PASSWORD (' biscuit ');

You must also use the password () function, but you do not need to use flush privileges.

Method Six

Use Grant ... Identified by statement

Mysql> GRANT USAGE On * * to [e-mail protected] "%" identified by ' biscuit ';

Here the password () function is unnecessary and does not require the use of flush privileges.

Note: PASSWORD () [NOT] Execute password encryption in the same way that the UNIX password is encrypted.

MySQL Forgot Password Solution

If MySQL is running, first kill it: Killall-term mysqld.

Start Mysql:bin/safe_mysqld--skip-grant-tables & You can go to MySQL without a password. Then there is

>use MySQL

>update User Set Password=password ("New_pass") where user= "root";

>flush privileges;

Kill MySQL again and start MySQL in a normal way.

12, Backup and recovery 12.1, backup

For example, to back up the AAA library created in the example above to file Back_aaa

[[email protected] root]# Cd/home/data/mysql (go to the library directory, this example library has been transferred from Val/lib/mysql to/home/data/mysql, see part VII above)

[Email protected] mysql]# mysqldump-u root-p--opt aaa > BACK_AAA

12.2. Recovery

[Email protected] mysql]# mysql-u root-p CCC < BACK_AAA

13, the use of mysqladmin utility

The Mysqladmin utility can be used to maintain MySQL's more general work (new, delete repositories, set user passwords and stop MySQL, and so on), with detailed instructions for viewing using mysqladmin--help. (Take the installation of this article as an example mysqladmin is located at/usr/local/mysql/bin/mysqladmin).

New Database Dbtest

#/usr/local/mysql/bin/mysqladmin-u Root-p Create dbtest

Enter Password:

Database "Dbtest" created.

deleting repositories

#/usr/local/mysql/bin/mysqladmin-u Root-p Drop dbtest

Enter Password:

Dropping the database is potentially a very bad thing to do.

Any data stored in the database would be destroyed.

Really want to drop the ' dbtest ' database [y/n]

Y

Database "Dbtest" dropped

Set the user password (change the password of Maa to 7654321,mysqladmin will ask for the original password of Maa first)

#/usr/local/mysql/bin/mysqladmin-u maa-p Password 7654321

Enter Password:

#

Stop MySQL Service

#./mysqladmin-u root-p Shutdown

Enter Password:

Note that after MySQL is shutdown, the following instructions must be executed by the operating system's root account to start MySQL:

/usr/local/mysql/share/mysql/mysql.server start

Modify MySQL default encoding the default code for MySQL is Latin1, does not support Chinese, then how to modify the default MySQL code, the following UTF-8 as an example to note that the need to modify the place is very many, the corresponding modification method is also many. Here is one of the simplest and most thorough methods: the Windows system
1. Abort MySQL Service
2, in the installation directory of MySQL found My.ini, if not the My-medium.ini copy as a my.ini can
3. After opening My.ini, add Default-character-set=utf8 under [client] and [mysqld], save and close
4. Start the MySQL service

Summary of MySQL usage under CentOS