Read "msyql full manual" 3: Management and Development

Part 3 Management
Chapter 4 Management and Configuration
Chapter 4 security, access control and permissions
Chapter 4 maintenance, backup, and recovery
Chapter 2 Performance Optimization
Chapter 2 MySQL Replication
Part 4 Development
Chapter 2 MySQL API
Chapter 2 MySQL and C
Chapter 2 MySQL and Perl
Chapter 2 MySQL and PHP

Based on the contents of this book, continue to write my summary.

The third part is for the system administrator or database administrator. If a system runs stably or not, the person in charge of the system plays a major role. Not everyone can afford it 7 × 24 hours a day, therefore, it is necessary to understand that it can improve a lot of performance during self-development. Therefore, in Chapter 3, we recommend that you read

Chapter 4 Management and Configuration
This chapter is a bit interesting. Just get to know something. MySQL comes with some management tools. You can use Google
1. mysqladmin, under/usr/local/MySQL/bin, you can use mysqladmin -- help to view specific parameters. However, it is more convenient to use other clients for management, such as phpMyAdmin.
2. Start and close. There is a script in the Unix System in the installation directory of MySQL/usr/local/MySQL/support-files/MySQL. server. sh, you can also use/usr/local/mysq. /bind the mysql_safe.windows98series directly contains mysqld.exe. Run MySql in two steps: MySQL 2000 and above. mysqld -- install and Net start MySQL, but I usually directly use services. MSC Management Panel to start and close
3. Check the server status
/Usr/local/MySQL/bin/mysqladmin status or version
Or, after entering MySQL, MySQL> select version () or show status;
4. Manage the client
Mysql> show processlist; you can see other users logging on to msyql and kill them easily.
5. Modify server configurations
The first method is to modify the my. CNF or my. ini file. UNIX is usually in the/etc/My. CNF or ~ /. My. CNF, while windows is generally in c: \ My. CNF. It is very easy to find it by yourself.
The second method is to directly use the set command, for example, set table_type = InnoDB. When Character Set garbled characters appear, it is generally solved through set names utf8.
6. Search for variable values
After entering MySQL, you can use show variables like 'charset _ * 'to view all character sets. The specific content can be searched by Baidu for "MySQL garbled characters", which should contain a lot of information.
7. Error Log
In UNIX, it is called hostname. Err. In Windows, it is mysql. Err.

Chapter 4 security, access control and permissions
1. Five-layer access control system: the user uses the user table, the host uses the host table, the database uses the DB table, the data table uses the tables_priv table, and the field uses the columns_priv table for access control, let's not talk more about it. In phpMyAdmin, you can use the "describe" command to control the table name.
2. Authorize, revoke, and browse
Permission revocation can be performed through grant and revoke, or directly in the preceding five tables of the MySQL database.
Browse through show grants for user @ host
3. Authorization using the grant and revoke commands is immediately activated, but the setting through regular SQL queries requires server reload (flush privileges) to take effect.
4. You can easily understand how to reset the root password if you forget the root password.
1) Stop the MySQL Service
2)/usr/local/MySQL/bin/safe_mysql -- skip-grant-tables -- skip-networking to bypass the authorization table
3) mysql> use MySQL;
Mysql> updata user SET Password = PASSWORD ('new-password') where user = 'root'
4) Restart msyql

Chapter 4 maintenance, backup, and recovery
Maintenance is mainly performed through different log files, including error logs, query logs, slow query logs, update logs, and binary update logs. I care about two types of logs: error logs and slow query logs.
To activate the error log, add -- log-error at startup.
/Usr/local/MySQL/bin/msyql_safe -- log-Error
To activate slow query logs, you must add -- log-Slow-queries at startup.
/Usr/local/MySQL/bin/msyql_safe -- log-Slow-queries
Slow query logs are used to find those queries that take a long time, and a corresponding solution is proposed.

Backup database mysqldump-u root-P Database Name> data file-d only backup format,-t only backup data
Recover database mysql-u root-P database name <data file or mysql> source data file or use load data infile. There is no need to know so much about Google.

Chapter 2 Performance Optimization
1. Use Indexes
2. subquery-> connection query or use variables
3. Adjust the server cache and memory buffer: Adjust the variable key_buff_size and table_cache.
4. Use the MySQL benchmark technology suite to adjust the server performance benchmark and evaluate the change results. The benchmark suite is called MySQL benchmark suite, but it is difficult to use it, who wants to be a database administrator?

Chapter 4 Database Replication
I used phpMyAdmin to export and import data directly or copy data directly, so I skipped this chapter. Haha

Part 4 Development
This book only introduces the development of MySQL in three languages: C, Perl, and PHP. In fact, various mainstream languages provide MySQL support. I am familiar with PHP, PHP official manual has a detailed introduction on it. You can refer to it here
MySQL is written in C. The C language is bundled with the MySQL release through the libmysqlclient library. MySQL has opened some APIs that can be called by language developers so that new languages can support mysql. However, this book is not detailed and we only need to know a rough idea.

After the conclusion, the overall architecture has been established. Although Google is used for specific knowledge in the future, it is different from reading it. Google can find more accurate keywords.
If you have time to study phpMyAdmin, I believe that this open-source project combining PHP and MySQL will increase your internal force ~