Advanced mysql courseware for PHP learning

MySQL database management database startup and shutdown, security, access control and permissions, database backup and recovery, import and export records MySQL database management database startup and shutdown, security, access control and permissions, database backup and recovery, import and export records

Responsibilities of the database administrator
Server startup and shutdown
User account maintenance
Log file maintenance
Database Backup and copy
Server Optimization
Software updates for the database management system
Security of data directories
Server Security
Disaster Recovery
Preventive maintenance
""""""""""""""""''
Database startup and shutdown
1. database startup:
Method 1: use the service command to start MySQL
# Service mysqld start
// Mysqld is the MySQL daemon and runs it to start the MySQL service.
Method 2: Use the mysqld script to start MySQL
#/Etc/init. d/mysqld start
Method 3: Use the safe_mysqld utility to start the MySQL service.
# Safe_mysqld &
Run safe_mysqld in the background using the & symbol.
"""""""""""""""""""''
Check whether MySQL is started:
# Service mysqld status
// Return the following information, indicating that the instance has been started.
Mysqld (pid 1663) is running...
// 1663 is the number of processes running mysqld, which may vary depending on the number of processes running in different systems
To restart MySQL, run one of the following commands:
# Service mysqld restart
#/Etc/init. d/mysqld restart
""""""""""""""""""''
2. database shutdown:
You can use one of the following commands:
# Service mysqld stop
#/Etc/init. d/mysqld stop
#/Mysqladmin shutdown


MySQL management
Modify the root management password
Method 1:
Mysql> set password for 'account' @ 'host' = old_password ('password ');
Update mysql. user set password = old_password ('password') where host = 'host' and user = 'account ';
Flush privileges;
Method 2:
Mysqladmin password 'crq'

User password
You can use the PASSWORD () and ENCRYPT () functions to ENCRYPT the MySQL PASSWORD.
Mysql> select PASSWORD ("alex ");
"23fc96e064be0017"
Note: ENCRYPT () is unavailable on Windows

Method 3: Use the update statement and password () function to set the root password to crp.
Mysql> update user set password = password ('crq ')
-> Where user = 'root ';
// The following message is returned, indicating that the authorization table user has been modified successfully.
Query OK, 2 row affected (0.09 sec)
Rows matched: 2 changed: 2 warnings: 0
Note: This method directly modifies the user in the authorization table, and the server only loads the permission settings in the authorization table at startup, therefore, you must use the flush privileges command in the mysql environment of the client program or use the flush-privileges sub-command of mysqladmin to notify the server to reload the authorization table.
Mysql> flush privileges; // you can use it immediately.
""""""""""""""""""""""'
Change the database storage path

In the Window, all MySQL databases are saved in the "% mysqlroor % \ data" directory.
Stop MySQL service
Modify the % systemroor % \ my. ini file
[Mysqld]
Datadir = D:/data
Move all files and folders in the original directory to the new directory D:/data.
Restart MySQL service

Modify MySQL character set
Find the MySQL configuration file my. ini, usually in C: \ window \ my. ini.
Add "default-character-set = gbk #" or gb2312, utf8 in the my. ini file
Restart MySQL service

Delete anonymous users:
Myslq> delete from user where user = '';
// The following message is returned, indicating that the anonymous user is successfully deleted.
Query OK, 2 row affected (0.03 sec)

Add new user permissions:
Use the grant statement to grant user permissions,
Syntax:
GRANT priv_type [(column_list)] [, priv_type [(column_list)]…]
ON {*. * | db_name. * | db_name.tabl_name | db_name}
TO user_name [identified by 'password']
[, User_name [identified by 'password']…]
[With grant option]


Create a new user
User name, which can contain up to 16 English characters
Create databases and tables that can be accessed by the user
Determine what operations the user is allowed to perform on the database
Determine which hosts/IP addresses the user is allowed to remotely connect
Allows the user to authorize or cancel authorization for other users.

For example, add a Super User crq that can connect to the MySQL server from a local host, but the password crqpass must be used for connection.
Mysql> grant all on *. * to crq @ localhost identfied by 'crqpass' with grant option;
// Return the following information, indicating that the permission is successfully set
Query OK, 0 rows affected (0.02 sec)
For example, you can use the same method to add a Super User crq that can be connected to the MySQL server from any other place, but the password crqpass must be used for connection.
Mysql> grant all on *. * to crq @ '%' identified by 'crqpass' with grant option;

Grant specific permissions to users:
Use the grant statement to add user1, the user of a MySQL server that can be connected from a local machine, and grant only select and insert permissions for the employee table in the sales database.
Mysql> grant select, insert on sales. employee to user1 @ localhost identified by 'user1pass ';
Note: after logging on to the database using # mysql-u user1-puser1pass, you can perform the following operations:
Mysql> use sales; // open the sales database
Mysql> show tables; // only the employee table is displayed.
Mysql> select * from employee; // You can view the data.
Mysql> insert into employee ....... // Insert an object.

Revoke user permissions:
Syntax: REVOKE priv_type [(column_list)] [, priv_type [(column_list)]…]
ON {*. * | db_name. * | db_name.tbl_name | db_name}
FROM user_name [, user_name…]
For example, revoke the insert permission of user1 for the employee table in the sales database:
Mysql> revoke insert on sales. employee from user1 @ localhost;
// Return the following information, indicating that the permission is successfully revoked.
Query OK, 0 row in set affected (0.00 sec)
""""""""""""""""""""""""""""""'
When the permission change takes effect
When mysqld is started, all authorization table content is read into the memory and takes effect from that point.
Modifications made to the authorization table using GRANT, REVOKE, or set password will be immediately noticed by the server.
If you manually modify the authorization table (using INSERT, UPDATE, and so on), you should execute a flush privileges statement or run mysqladmin flush-privileges to tell the server to load the authorization table again, otherwise, your change will not take effect unless you restart the server.

Impact of permission change
When the server notices that the authorization table has been changed, the existing customer connection has the following impact:
The table and column permissions take effect in the next request of the customer.
The database permission change takes effect in the next USE db_name command.
Changes in global permissions and passwords take effect the next time a customer connects.

Database Backup and Recovery
1. database backup:
A. back up the database with mysqldump:
Syntax: mysqldump [OPTIONS] database [table]
Instance:
Back up the database sales to the sales_bak030630. SQL file.
# Mysqldump-u root-p -- opt sales> sales_bak030630. SQL
// The following message is returned, indicating that the backup is successful.
-Rw-r -- 1 root 2296 Jun 29 23:30 sales_bak030630. SQL

B. back up the database using the direct copy method:
Another way to back up databases or tables without using mysqldump is to directly copy table files. You can usually use utilities such as cp, tar, or cpio. When using the direct-copy backup method, make sure that these tables are not used. If the server is modifying a table while copying it, the copy is invalid.

The best way to ensure copy integrity is to close the server, copy files, and restart the server.


2. database recovery:
When the database system crashes, use the latest backup to restore the database. if the update log is enabled, you also need to re-execute any queries for database modifications after the last backup in the update log to restore the data to the state at the time of crash as much as possible.
# Mysql-u root-p sales <sales_bak030630. SQL

Optimization table
In most cases, table optimization is worthwhile. When a table is modified multiple times, its structure will soon become fragmented, resulting in performance degradation. In this case, you can use the optimize table command to refresh and clear the space.
Mysql> optimize table tablename;

Import and Export records
The INSERT statement is not the only method to INSERT records into a table. MySQL also allows the load data infile command to INSERT multiple records at a time. This command reads the raw data from a text file (the file can be placed on the connection server or the client) and analyzes the data based on the column and row-defining symbols, then, an INSERT statement is automatically generated to write data to a table.


Import data
By default, MySQL considers that the DATA file is on the server and specifies the location in the load data infile statement.
If you want to use data on the client, you can add the LOCAl keyword in the statement to tell MySQL to search for files on the client's file system.
Mysql> load data local infile 'C: \ data.txt'
Into table user
Fields terminated by ', 'lines terminated by' \ n ';

You 'd better add the following command before this
Mysql> load data LOW_PRIORITY INFILE 'data.txt 'into table user;
Of course, there are also keywords such as ignore replace.

Export data
If you do not specify a FIELDS clause, the default value is the same as that written in this way:
Fields terminated by '\ t' enclosed by ''escaped '\\'
If no LINES clause is specified, the default value is the same as that written in this case:
Lines terminated by '\ n'
In other words, when the default value causes reading input, load data infile performs as follows:
Search for line boundary at line breaks
Divide the row into fields at the location operator
Do not expect fields to be enclosed by any quotation marks
Part of the literal characters starting with "\", including the positioning character, line break, and "\".

Export data
Conversely, the default value causes SELECT... into outfile to behave as follows when writing data to the output:
Write a locator between fields
Enclose fields without any quotation marks
Use "\" to escape the location, line break, or "\" characters that appear in the field
Line feed at the end of a row
Note: To write fields escaped by '\', you must specify two backslash values for the value read as a single backslash.