Some knowledge about MySQL

MySQL user management
The MySQL administrator should know how to set the MySQL user account and specify which user can connect to the server and where
. MySQL 3.22.11 introduces two statements to make this work easier.
Run the grant statement to create a MySQL user and specify its permissions, while the revoke statement deletes the permissions. Play two statements
The front-end role of the MySQL database, and provides another method that is different from directly operating the contents of these tables.
Create and revoke statements affect four tables: Authorization Table
Content
Users can connect to users on the server and have any global permissions.
Database-level Permissions
Tables_priv table-level Permissions
Columns_priv column-level permission

There are 5th other authorization tables (hosts), but they are not affected by Grant and revoke.
When you issue a grant statement to a user, create a record for the user in the User table. If
Specify any global permissions (administrative permissions or permissions applicable to all databases). These are also recorded in the User table.
. If you specify database, table, and column-level permissions, they are recorded in dB, tables_priv, and
Columns_priv table.
Using Grant and revoke is easier than directly modifying the authorization table. However, we recommend that you read MySQL Security
Guide. These tables are exceptionally important. As an administrator, you should understand how they surpass Grant and
The function level of the revoke statement.
In the following sections, we will introduce how to set up and authorize a MySQL user account. We also involve how to revoke permissions.
And delete users from the authorization table.
You may also want to use the mysqlaccess and mysql_setpermission scripts, which are one of
Part, which is a Perl script that provides another option for grant statements to set user accounts.
DBI support is required for mysql_setpermission.
1. Create and authorize a user

The syntax of the grant statement looks like this:
Grant privileges (columns) on what to user identified by "password"
With grant option

To use this statement, you must enter the following parts:
Privileges

The following table lists the permissions that can be used for grant statements:
Permission specifier
Permitted operations
Alter table and Index
Create Database and table Creation
Delete Delete existing records in the table
Drop discard (delete) databases and tables
Create or discard an index
Insert Insert a new row into the table
Reference unused
Select to retrieve records in a table
Update modify existing table records
File: reads or writes files on the server.
Process: View information about the thread executed on the server or kill the thread.
Reload: Reload the authorization table or clear logs, host caches, or table caches.
Shutdown the server
All; All privileges Synonyms
Usage special "no permission" permission

The table above shows that the permission specifiers in the first group apply to databases, tables, and columns, and the second group manages permissions. Average,
These are relatively strictly authorized because they allow users to affect server operations. The third group has special permissions,
All means "All Permissions". uasge means that you have no permissions, that is, you create a user, but do not grant permissions.
Columns

The permission column is optional, and you can only set specific permissions for the column. If the command has more than one
Columns, which should be separated by commas.
What

Permission usage level. The permission can be global (applicable to all databases and tables) and specific databases.
(Applicable to all tables in a database) or specific tables. You can specify a columns statement as permission.
Limits are column-specific.
User

The user authorized by the permission, which consists of a user name and host name. In MySQL, you not only specify who can connect
And where to connect. This allows two users with the same name to connect from different places. MySQL allows you to differentiate them
And grant permissions to each other independently.
A user name in MySQL is the user name specified when you connect to the server. This name does not need to be logged on with your UNIX
Connect the recording name or Windows Name. By default, if you do not specify a name explicitly, the client program will use
Your login name is the MySQL user name. This is just an agreement. You can change the name in the authorization table
And then connect to the nobody to execute the operation that requires the superuser permission.
Password

The password assigned to the user. It is optional. If you do not specify the identified by clause for a new user, use
The user is not assigned a password (Insecure ). For existing users, any password you specify will replace the old password. If you do not
Password. The old password remains unchanged. When you use identified by, the password string is replaced with the password.
Grant will encode the password for you. Do not use the password () function as you do with set password.
Number.
The with grant option clause is optional. If you include it, you can grant the permission to use the grant statement.
Grant permissions to other users. You can use this clause to grant permissions to other users.
The username, password, database, and table name are case sensitive in the authorization table record, and the host name and column name are not.

Generally, you can identify the types of grant statements by asking a few simple questions:
Who can connect from there?
What level of permissions should users have and what do they apply?
Should the user be allowed to manage permissions?

The following are some examples.
1.1 who can connect and connect from there?

You can allow a user to connect from a specific host or a series of hosts. There is one extreme: If you know that demotion is from
Host connection, you can restrict permissions to a single host:
Grant all on samp_db. * to Boris @ localhost identified by "Ruby" grant all on
Samp_db. * To fred@res.mars.com identified by "quartz"

(Samp_db. * indicates "all tables in the samp_db database.) Another extreme is that you may have a regular trip.
And user max that can be connected from hosts around the world. In this case, you can allow
Connection:
Gran

MySQL management tool: phpMyAdmin
From: ohaha learning experience [ohaha.ks.edu.tw] by: blue bubble (panda@ks.edu.tw)
Preface:
PhpMyAdmin is a MySQL management tool.
Through this program, you can directly manage MySQL from the web without running it on the system.
Installation steps:
1. Obtain the archive token
The only difference is that the latter does not need to be optimized in Chinese.
The so-called Chinese optimization is because the translator of this program may be from the Chinese mainland.
You can ignore it if you think it is okay.
2. We should first use the former and then add the Chinese optimization.
3. decompress the file to the root directory of the Web server.
It means that the place where you place the web page
Ex:/usr/local/Apache/htdocs/(this is where my webpage is stored)
A. # mv phpmyadmin_2.0.5.tar.gz/usr/local/Apache/htdocs/move to the root directory of the file
B. # tar zxvf phpmyadmin_2.0.5.tar.gz decompress phpmyadmin_2.0.5.tar.gz
C. Path/usr/local/Apache/htdocs/phpMyAdmin
D. modify the configuration file.
# Vi config. Inc. php3
Find the following section
$ Login servers [1] ['host'] = 'localhost'; // MySQL hostname
$ Define servers [1] ['Port'] = ''; // MySQL port blank indicates default 3306
$ Incluservers [1] ['adv _ auth'] = true; // whether advanced functions are used
$ Login servers [1] ['stduser'] = 'root'; // MySQL Administrator
$ Login servers [1] ['stdpass'] = '000000'; // password of the MySQL Administrator
// I use root as the Administrator, and the password is 123456. You can use your favorite password.
4. Test
Open the browser and enter the http: // URL/PHPmyAdmin/
Done...
Chinese Optimization
If the file you just used is c_phpmyadmin_2.0.5.tar.gz, there is no problem with Chinese characters.
If you do what I do, please get the Chinese optimization file (ftp://ohaha.ks.edu.tw/pub/source/php/chinese_big5.inc.php3)
Place this file in the phpMyAdmin Directory, which replaces chinese_big5.inc.php3.
If you think it is not safe, you can change the name of the original chinese_big5.inc.php3 and then put the new file
A. Mv chinese_big5.inc.php3 chinese_big5.inc.php3.old
// Rename it chinese_big5.inc.php3.old
B. Mv chinese_big5.inc.php3/usr/local/Apache/htdocs/PHPmyAdmin/
Tips
Smart do you find anything wrong with it? If everyone does what I do
So... is it because everyone's phpMyAdmin location is at http: // URL/phpMyAdmin?
In this case, you can change the name of the phpmyamin folder.
If I want to change the name to PMA (one word for each word to facilitate memory)
A. Path:/usr/local/Apache/htdocs/
B. # mv phpMyAdmin PMA
In this way, the location of phpMyAdmin cannot be solved by others. You only need to know it...

MySQL Security Guide

A MySQL System Administrator is responsible for maintaining the data security and integrity of your MySQL database system. This article mainly introduces how to build a secure MySQL system and provides you with a guide from both internal and external networks.

This article focuses on the following security issues:

Why is security very important? What attacks should you guard against?
What are the risks (Internal Security) faced by servers?
How does one deal with the client risk (External Security) of the server?
The MySQL Administrator is responsible for ensuring the security of the database content, so that these data records can only be accessed by those authorized users, which involves the internal and external security of the database system.
Internal security concerns the file system level, that is, preventing Mysql Data Directories (datadir) from being attacked by persons (legal or stolen) with accounts on the server host. If the permissions on the data directory content are excessively granted so that everyone can simply replace the files corresponding to those database tables, it makes no sense to make sure that the authorized tables that control customer access over the network are correctly set.

External security concerns customers who connect to the server from the external network, that is, to protect the MySQL server from attacks from the connection to the server through the network. You must set the MySQL authorization table so that they are not allowed to access the database content managed by the server, unless a valid user name and password are provided.

The following describes in detail how to set up the file system and the authorization table MySQL to achieve two levels of MySQL security.

I. Internal Security-ensures the security of data directory access
The MySQL server provides a flexible permission system through the authorization table in the MySQL database. You can set the content of these tables to allow or deny access to the database. This provides a security means to prevent unauthorized network access from attacking your database, however, if other users on the host can directly access the data directory content and establish good security for accessing the database through the network, unless you know that you are the only user logging on to the MySQL server to run the host, you need to be concerned about the possibility that other users on this machine can access the data directory.

The following content should be protected:

Database files. Obviously, you need to maintain the private usage of the database managed by the server. Database owners usually consider the security of database content, even if they do not want to, they should also consider the openness of database content, rather than exposing this content through poor security of Data Directories.
Log File. Generally, logs must be updated to ensure security because they contain query text. Anyone with access to log files can monitor operations performed by the database.
The log file security is also documented in queries such as grant and set password. Generally, the log updates contain sensitive query text, including passwords (MySQL uses password encryption, however, it is used for subsequent connection establishment only after the settings have been completed. The process of setting a password is designed to be a query such as grant or set password, and these queries are recorded in the log file as common text ). If an attacker has the same read permission as a daily file, he only needs to run grep on the log file to find sensitive information by searching for words such as grant and password.
Obviously, you do not want other users on the server host to have the write permission for database directory files, because they can rewrite your status files or database table files, but the read permission is also dangerous. If a database table file can be read and the file is stolen and Mysql itself is obtained, it is also troublesome to display the table content in plain text. Why? Because you need to do the following:

Install your own "special" MySQL server on the server host, but there is a port, socket, and data directory different from the official server version.
Run mysql_install_db to initialize your data directory, which grants you the permission to access your server as the MySQL Root User. Therefore, you have full control over the server access mechanism and it also creates a test database.
Copy the table files you want to steal to the test directory in the database directory of your server.
Start your server. You can access database tables at will. Show tables from test shows that you have a copy of a stolen table, and select * shows all the contents of any of them.
If you are really vicious, publish the permission to any anonymous user on your server so that anyone can connect to the server from any address to access your test database. You have now published the stolen database tables.
Consider, from the opposite perspective, Do you want others to treat you like this? Of course not! You can run the LS-l command in the database directory to check whether your database contains insecure files and directories. Search for files and directories with "group" and "other users" permissions. The following is a list of insecure Data Directories:

　
% Ls-l
Total 10148
Drwxrwxr-x 11 mysqladm wheel 1024 May 8.
Drwxr-XR-x 22 root wheel 512 May 8 ..
Drwx ------ 2 mysqladm mysqlgrp 512 Apr 16 menagerie
Drwxrwxr-x 2 mysqladm wheel 512 Jan 25 MySQL
Drwxrwxr-x 7 mysqladm wheel 512 Aug 31 1998 SQL-statements
Drwxrwxr-x 2 mysqladm wheel 1536 May 6 Test
Drwx ------ 2 mysqladm mysqlgrp 1024 May 8 TMP
....

As you can see, some databases have the correct permissions, while others are not. This example is the result after a period of time. For more limited permissions, you can set them on the server of an earlier version that is less restrictive than the updated version (note that the more restrictive directories menageria and TMP both have a relatively recent date ). The current MySQL version ensures that these files can only be read by users on the running server.

Let's modify these permissions so that only server users can access them. Your primary protection tool comes from the setting file and directory owner and mode provided by the UNIX file system itself

Mysql Data Import and Export

Now MySQL is used more and more, and I also use it to make my own message board. In the process of use, it is necessary to gradually master its management functions, not only me, but also the requirements of many netizens. There are some questions about how to export data from MySQL for use locally or on other database systems, and how to import existing data into MySQL databases. Now, let me summarize these two questions. The content is not very detailed. In fact, the MySQL manual is very detailed, but I have extracted these two aspects and added a little understanding of myself. For more details, see the relevant chapters of the database.

Data Export
Data export can be performed in the following ways:
Use the select into OUTFILE "FILENAME" Statement
Use the mysqldump Utility
Use the select into OUTFILE "FILENAME" Statement
You can execute it in the MySQL command line or in the PHP program. The following uses the MySQL command line as an example. 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 the file name must be enclosed in single quotes. 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 may 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 ). Then we can modify the directory of the output file name to put it 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,
The "/a.txt" file is stored in the C:/directory. Therefore, the SELECT command considers the current directory as the database storage directory. Here 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 escaping. Lines terminated indicates what is used to separate records in each row.
The default values listed above are optional. If this parameter is not selected, the default values are used. It can be modified as needed. An example is provided as follows:
Mysql> select * from driver into OUTFILE "a.txt" fields terminated by "," 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.

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 want to upload data from the local server to the server, you can upload the file, and then use the command line method on the server.