MySQL usage in Centos

1. Install MySQL

Install mysql under Centos please click on: http://www.centoscn.com/CentosServer/ SQL /2013/0817/1285.html

Ii. Several important MySQL Directories

After MySQL is installed, its database files, configuration files, and command files are not installed in the same directory as SQL Server by default. It is very important to understand these directories, especially for Linux beginners, the directory structure of Linux itself is complicated. If you cannot figure out the installation directory of MySQL, you won't be able to learn it in depth.

The following describes these directories.

2.1 database directory

/Var/lib/mysql/

2.2 configuration file

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

2.3 related commands

/Usr/bin (commands such as mysqladmin mysqldump)

2.4 Startup Script

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

3. log on to MySQL
3.1 connect to the local MySQL

Example 1: connect to MYSQL on the local machine.

First, open the DOS window, enter the directory mysqlbin, then type the command mysql-uroot-p, and press enter to prompt you to enter the password. If you have just installed MYSQL, super User root has no password, so press enter to enter MYSQL. The MYSQL prompt is: mysql>.

3.2 connect to remote MySQL

Example 2: connect to MYSQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: you do not need to add spaces for u and root. The same applies to others)

3.3 exit MYSQL

Command: exit (Press ENTER ).

4. Change the logon Password

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

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

4.1 change password for the first time

Example 1: Add a password ab12 to the root user. First, enter the directory mysqlbin in DOS, and then type the following command:

Mysqladmin-uroot-password ab12

Note: because the root account does not have a password at the beginning, the old-p password can be omitted.

4.2 Change Password again

Example 2: Change the root password to djg345.

Mysqladmin-uroot-pab12 password djg345

5. Add users

(Note: Unlike the above, the following commands in the MySQL environment are followed by a semicolon as the command Terminator)

Format: grant select on database. * to username @ login host identified by \ "password \"

Example 1: Add a user named "test1" with the password "abc" so that the user 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:

Grant select, insert, update,
Delete on *. * totest1 @ \ "% \" Identified by \ "abc \";

However, the User Added in Example 1 is very dangerous. If someone knows the password of test1, then he can log on to your MySQL database on any computer on the internet and do whatever he wants for your data. For the solution, see Example 2.

Example 2: Add a user named "test2" with the password "abc" so that the user can only log on to localhost, you can also 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 the user knows the password of test2, he cannot access the database directly from the internet, but can only access the database through the web pages on the MySQL host.

Grant select, insert, update,
Delete on mydb. * to test2 @ localhost identified by \ "abc \";

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

Grant select, insert, update, delete on mydb
. * To test2 @ localhost identified \"\";

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)

6. Start and Stop

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

6.2 stop

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

6.3 automatic start 6.3.1. Check whether mysql is in the Automatic Start List.

[Root @ test1 local] #/sbin/chkconfig-list

6.3.2 add MySQL to the startup Service Group of your system

[Root @ test1 local] #/sbin/chkconfig-add mysql

6.3.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:

7.1 create a data directory under the home Directory

Cd/home mkdir data

7.2 stop the MySQL service process:

Mysqladmin-u root-p shutdown

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

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

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

7.6 modify 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.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.

8.1 MySQL Common Operation Command 8.1.1. display the Database List:

Show databases;

At the beginning, there were only two databases: mysql and test. The MySQL database contains the MYSQL system information. We change the password and add new users to use this database for operations.

8.1.2. display the data tables in the database:

Use mysql; // open the database. If you have learned FOXBASE, you will not be unfamiliar with it.

Show tables;

8.1.3 display the data table structure:

Describe table name;

8.1.4. database creation:

Create database name;

8.1.5 create a table:

Use Database Name;

Create table Name (field setting list );

8.1.6 Delete databases and tables:

Drop database name;

Drop table name;

8.1.7 clear records in the table:

Delete from table name;

8.1.8. display the records in the table:

Select * from table name;

8.1.9 add record

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.1.10 modify records

For example, change the date of birth of John

Mysql> update name set csny = '2017-01-10 'where xm = 'zhang san ';

8.1.11 delete records

For example, delete the records of Michael Jacob.

Mysql> delete from name where xm = 'zhang san ';

8.2. An instance for creating a database, creating a table, and inserting data

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

Create database school; // create a database SCHOOL

Use school; // open the SCHOOL library

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

); // Table creation ends

// Insert fields as follows

Insert into teacher values ('', 'glengang ', 'shenzhen Zhongyi', '2017-10-10 ');

Insert into teacher values ('', 'jack', 'shenzhen Zhongyi ', '2017-12-23 ');

Note: In the table in progress (1), set the ID to a numeric field of 3: int (3) and make it automatically add one: auto_increment for each record. It cannot be blank: not null and set it as the main field primary key (2) Set NAME to the character field with a length of 10 (3) set ADDRESS to the character field with a length of 50, the default value is Shenzhen. What is the difference between varchar and char? It will only be discussed later. (4) set YEAR as the date field.

If you type the preceding command at the MySQL prompt, debugging is not convenient. You can write the above commands into a text file as they are. SQL, then copy to c :\\, and enter the directory \ mysql \ bin in DOS status, and then type the following command:

Mysql-uroot-p password <c: \ school. SQL

If it succeeds, no display is displayed for a blank row. If there is an error, a prompt is displayed. (The preceding command has been debugged. You only need to remove the // annotation to use it ).

9. Modify the database structure

9.1 field operations
9.1.1 Add a field

Alter table dbname add column <field Name> <field Options>

9.1.2 modify Fields

Alter table dbname change <old field Name> <new field Name> <option>

9.1.3 delete a field

Alter table dbname drop column <field Name>

10. Data Export

Data export can be performed in the following ways:

Use the select into outfile "filename" Statement

Use the mysqldump Utility

10.1 use the select into outfile "filename" Statement

You can execute it in the mysql command line or in the php program. I will use the mysql command line as an example below. When used in php, change it to the corresponding query for processing. However, when using this command, you must have the file permission. For example, we have a database named phptest, and one of the tables is driver. Now we need to unload the driver into a file. Run the following command:

Mysql> use phptest;

Database Changed

Mysql> select * from driver into outfile "a.txt ";

Query OK, 22 rows affected (0.05 sec)

Then, the table driverts from the data warehouse to the.txt file. Note that you must add a single quote to the file name. So where is this file? There is a data directory under the mysql Directory, which is the place where the database files are stored. Each database occupies a separate subdirectory, so the phptest directory is c: \ mysql \ data \ phptest (Note: My mysql is installed under c: \ mysql ). Well, now we are going in. a.txt is it. Open this file, which can be:

1 Mika Hakinnen 1

2 David Coulthard 1

3 Michael Schumacher 2

4 Rubens Barrichello 2

...

There may be many records. Each field is separated by a tab (\ t ). We can modify the directory of the output file name so that it can be placed in the specified location. For example, "a.txt" can be changed to "./a.txt" or "/a.txt ". "./A.txt" is stored in the c: \ mysql \ data directory, while "/a.txt" is stored in the c: \ directory. Therefore, the select command assumes that the current directory is the database storage directory, which is c: \ mysql \ data.

You can also use the select command to specify the delimiter between fields and escape characters, including characters and line delimiter. Column:

FIELDS

Terminated by "\ t"

[OPTIONALLY] enclosed ""

Escaped ""

LINES

Terminated by "\ n"

TERMINATED indicates that fields are separated.

[OPTIONALLY] ENCLOSED indicates the characters used to include a field, if OPTIONALLY is used, only CHAR and VERCHAR are included with ESCAPED, which indicates what is used as the escape character when escape is required. lines terminated indicates what is used to separate records in each row. The default value is used to separate the above columns, these items are optional. If you do not select them, the default value is used. It can be modified as needed. An example is provided as follows:

Mysql> select * from driver into outfile "a.txt" fields terminated ","

Enclosed """;

Query OK, 22 rows affected (0.06 sec)

The result may be as follows:

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

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

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

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

...

We can see that each field is separated with "," and each field is included. Note that the line record delimiter can be a string. Please test it yourself. However, if the output file exists in the specified directory, an error is reported. delete the file before testing.

10.2 use the mysqldump Utility

From the preceding select method, we can see that the output file only contains data but does not have a table structure. Moreover, it is not easy to process only one table at a time. However, you can write the select command into an SQL file (it should be easy to copy the text), and then execute the command line: mysql database name is the simplest:

Mysqldump phptest> a. SQL

Possible results:

# MySQL dump 7.1

#

# Host: localhost Database: phptest

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

# Server version 3.22.32-ware-debug

#

# Table structure for table "driver"

#

Create table driver (

Drv_id int (11) DEFAULT "0" not null auto_increment,

Drv_forename varchar (15) DEFAULT "" not null,

Drv_surname varchar (25) DEFAULT "" not null,

Drv_team int (11) 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 multiple tables exist, they are listed below. You can see that this file is a complete SQL file. If you want to import it to other databases, you can use the command line method to conveniently: mysql phptest <a. SQL. If you upload the data from the local server to the server, you can upload the file and load the data on the server using the command line method.

If you only want to unload the table creation command, the command is as follows:

Mysqldump-d phptest> a. SQL

If you only want to unload the SQL command for inserting data without the table creation command, the command is as follows:

Mysqldump-t phptest> a. SQL

What should I do if I only want data and do not want any SQL commands?

Mysqldump-T./phptest driver

Only when the-T parameter is specified can the plain text file be detached, indicating the directory where the data is detached and./indicates the current directory, that is, the same directory as mysqldump. If no driver table is specified, the data of the entire database is detached. Each table generates two files, one of which is a. SQL file, including table creation and execution. The other is a. txt file that only contains data and does not contain SQL commands.

You can specify the field separator, including characters, escape fields, and line record separators, in the same way as the select method. The parameters are listed below:

-- Fields-terminated-by = field separator

-- Fields-enclosed-by = field inclusion character

-- Fields-optionally-enclosed-by = field inclusion character, only used in CHAR and VERCHAR fields

-- Fields-escaped-by = Escape Character

-- Lines-terminated-by = line record Separator

I think you should understand what these parameters mean. An example is as follows:

Mysqldump-T./-- fields-terminated-by =, -- fields-enclosed-by = "phptest driver

Output result:

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

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

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

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

...

Note the usage of characters.

10.3 Summary

The preceding method uses the select and mysqldump utilities to unload text. Select is suitable for processing using programs, while mysqldump is manual. It also provides powerful export functions and can process the entire database or multiple tables specified in the database. You can use it as needed.

There are also some methods, such as copying database files directly, but the database system after moving should be consistent with the original system. I will not mention it here.

11. Data Import

Similar to export, there are two ways to import data:

Use the load data infile "filename" command

Use the mysqlimport Utility

Use SQL files

Since the first two processes are similar to the export operations, they are only inverse operations. Therefore, we only provide examples for using several commands and do not explain them. You can refer to the manual on your own.

11.1 use the load command:

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

Enclosed """;

11.2 use the mysqlimport utility:

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

11.3 use SQL files

You can use the SQL file exported by mysqldump to execute the mysql database name in the command line.

First, you must declare that, in most cases, modifying MySQL requires the root permission in mysql. Therefore, you cannot change the password unless you request the Administrator.

Method 1

Use phpmyadmin, which is the simplest. Modify the user table of the mysql database, but do not forget to use the PASSWORD function.

Method 2

Use mysqladmin, which is a special case stated above.

Mysqladmin-u root-p password mypasswd

After entering this command, you need to enter the original root password, and then the root password will be changed to mypasswd. Change the root in the command to your username, and you can change your password. Of course, if your mysqladmin cannot connect to mysql server, or you cannot execute mysqladmin, this method is invalid. In addition, mysqladmin cannot clear the password.

The following methods are used at the mysql prompt and must have the root permission of mysql:

Method 3

Mysql> insert into mysql. user (Host, User, Password)

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

Mysql> FLUSH PRIVILEGES

Specifically, this is adding a user with the username jeffrey and password biscuit. I wrote this example in mysql Chinese user manual. Be sure to use the PASSWORD function, and then use flush privileges.

Method 4

Similar to method Sany, but the REPLACE statement is used.

Mysql> replace into mysql. user (Host, User, Password)

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

Mysql> FLUSH PRIVILEGES

Method 5

Use the set password statement,

Mysql> set password for jeffrey @ "%" = PASSWORD ('biscuit ');

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

Method 6

Use the GRANT... identified by statement

Mysql> grant usage on *. * TO jeffrey @ "%" identified by 'biscuit ';

Here, the PASSWORD () function is unnecessary and does not need to be flush privileges.

Note: PASSWORD () [not] implements PASSWORD encryption in the same way as Unix PASSWORD encryption.

How to fix MySQL password loss

If MySQL is running, killall-TERM mysqld is first killed.

Start MySQL: bin/safe_mysqld -- skip-grant-tables & and you can enter MySQL without a password. Then

> 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, 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

12.2 recovery

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

XIII. Use of mysqladmin Utility

The mysqladmin utility can be used to maintain MySQL's general tasks (such as adding and deleting databases, setting user passwords, and stopping MySQL). For detailed instructions, you can use mysqladmin -- help to view them. (Taking the installation in this article as an example, mysqladmin is in/usr/local/mysql/bin/mysqladmin ).

Add database dbtest

#/Usr/local/mysql/bin/mysqladmin-u root-p create dbtest

Enter password:

Database "dbtest" created.

Delete Database

#/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 will be destroyed.

Do you really want to drop the 'dbtest' database [y/N]

Y

Database "dbtest" dropped

Set the user password (change the password of the locally deployed account to 7654321, and mysqladmin will first ask the original password of the locally deployed account)

#/Usr/local/mysql/bin/mysqladmin-u PAA-p password 7654321

Enter password:

#

Stop MySQL Service

#./Mysqladmin-u root-p shutdown

Enter password:

Note: after shutdown MySQL, the following commands must be executed by the root account of the job system to start MySQL:

/Usr/local/mysql/share/mysql. server start

Modify MYSQL Default encoding MySQL Default encoding is Latin1, does not support Chinese, so how to modify the default encoding of MySQL, the following UTF-8 as an example to illustrate the need to pay attention to is, there are many places to modify, and there are many corresponding modification methods. The following is the simplest and most thorough method: Windows
1. Stop the MySQL Service
2, in the installation directory of MySQL find my. ini, if not, copy the my-medium.ini as a my. ini
3. After opening my. ini, add default-character-set = utf8 under [client] and [mysqld], save and close
4. Start the MySQL Service