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 the duties 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;