mysql管理和配置

一、mysql数据库的管理
1 mysql附带了很多工具，专门设计用来提高这个过程的速度，使得工作效率更高，其中一个重要的工具成为mysqldump实用程序，它可以写mysql的表结构，表数据或者两个表同时写，以便备份各种不同格式的文件。mysqldump的输出可以很容易的使用，从而能快速恢复一个或多个mysql数据库，其方法是使用mysqlimport或mysql客户机命令行方式，或者通过loaddatainfile命令。在表被破坏的情况下，mysql可以通过一个恢复实用程序的套件改善数据恢复的机会，这些实用程序十分擅长深入被破坏表的内部，或者完全恢复这个表，或则修理被破坏的点，这样可以使得大多数数据得到恢复。
mysql还配备了内置复制，可以根据预先定义的主次关系，从一个数据库服务器向另外一贯饿服务器镜像产生的变化。在mysql的早期版本只能支持单向复制，现在比较新的版本能够支持双向复制，可以更为灵活的进行镜像和保证负载平衡。
2 安全和访问控制
mysql配备了十分完善的访问控制和权限系统，防止未经授权的用户访问系统。mysql采用了５层权限结构，可以创建十分庞大的访问控制。这些规则在决定如何处理具体用户申请时供mysql使用：
* 如果与mysql权限系统中规定的访问原则匹配，则允许连接服务器。这种访问规则可以以用户和主机为基础确定，用来现在来自确定子网或ip地址范围以外的主机的访问。而这种与服务器的连接只能在用户提供了正确的口令后才能得到。
* 一旦建立连接，mysql将坚持用户执行的每个动作，确认是否得到实施该动作的足够的权限。用户可以被限制只能在具体数据库或字段中操作，mysql甚至可以控制用户能够在数据库、表或字段层面上运行那些类型的查询。通过对用户口令的单向加密方案，系统的安全得到了进一步的加强，mysql４x以后的版本可以支持ssl传输。
3 性能优化
mysql可以满足很多具体需要，mysql童年系统变量产生很多内部参数，并且允许开发者和管理员很容易的进行修改，以便满足定制要求，很多会降低系统性能的功能，如事务、参照完整性和被保存的程序等，这些参数都可以被允许或被禁止，这样可以针对每个应用程序选择功能和性能的优化方案。例如analyze table和explain select命令可以帮助mysql开发者设置查询的基准点，寻找性能的瓶颈，并且有新性能，如query cache(可以缓存查询结果，将缓存数据退给调用者，而不必在每次查询时重新执行）可以在减少额外编程时帮助改善性能。
在讨论性能时的一个重要的因素是可扩展性，很多数据库系统在出来数千条记录时工作的很好，但是在数量增加到数以百万计时，其性能就大幅的降低。然而使用mysql不会出现这种问题。
二、基本的服务器管理和配置任务
通常用mysqladmin工具来执行mysql的管理，这个命令的格式为：
mysqladmincommand
mysqladmin支持的命令如下：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
命令  作用
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
status  返回服务器状态信息
password 修改用户口令
shutdown 关闭mysql服务器
reload  重载mysql授权表
refresh  重置所有的高速缓存和日志
variables 返回所有服务器变量值
version  返回服务器版本
processlist 返回服务器上所有进程的列表
kill  取消一个服务器进程
ping  测试服务器是否处于活动状态
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
运行mysqladmin--help可以获得命令的完整列表。
mysql> show status;
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 0        |
| Aborted_connects                  | 0        |
| Binlog_cache_disk_use             | 0        |
| Binlog_cache_use                  | 0        |
| Bytes_received                    | 58       |
| Bytes_sent                        | 72       |
| Com_admin_commands                | 0        |
| Com_alter_db                      | 0        |
| Com_alter_table                   | 0        |
| Com_analyze                       | 0        |
| Com_backup_table                  | 0        |
| Com_begin                         | 0        |
| Com_change_db                     | 0        |
| Com_change_master                 | 0        |
| Com_check                         | 0        |
| Com_checksum                      | 0        |
| Com_commit                        | 0        |
| Com_create_db                     | 0        |
| Com_create_function               | 0        |
| Com_create_index                  | 0        |
| Com_create_table                  | 0        |
| Com_dealloc_sql                   | 0        |
| Com_delete                        | 0        |
| Com_delete_multi                  | 0        |
| Com_do                            | 0        |
| Com_drop_db                       | 0        |
| Com_drop_function                 | 0        |
| Com_drop_index                    | 0        |
| Com_drop_table                    | 0        |
| Com_drop_user                     | 0        |
| Com_execute_sql                   | 0        |
| Com_flush                         | 0        |
| Com_grant                         | 0        |
| Com_ha_close                      | 0        |
| Com_ha_open                       | 0        |
| Com_ha_read                       | 0        |
| Com_help                          | 0        |
| Com_insert                        | 0        |
| Com_insert_select                 | 0        |
| Com_kill                          | 0        |
| Com_load                          | 0        |
| Com_load_master_data              | 0        |
| Com_load_master_table             | 0        |
| Com_lock_tables                   | 0        |
| Com_optimize                      | 0        |
| Com_preload_keys                  | 0        |
| Com_prepare_sql                   | 0        |
| Com_purge                         | 0        |
| Com_purge_before_date             | 0        |
| Com_rename_table                  | 0        |
| Com_repair                        | 0        |
| Com_replace                       | 0        |
| Com_replace_select                | 0        |
| Com_reset                         | 0        |
| Com_restore_table                 | 0        |
| Com_revoke                        | 0        |
| Com_revoke_all                    | 0        |
| Com_rollback                      | 0        |
| Com_savepoint                     | 0        |
| Com_select                        | 0        |
| Com_set_option                    | 0        |
| Com_show_binlog_events            | 0        |
| Com_show_binlogs                  | 0        |
| Com_show_charsets                 | 0        |
| Com_show_collations               | 0        |
| Com_show_column_types             | 0        |
| Com_show_create_db                | 0        |
| Com_show_create_table             | 0        |
| Com_show_databases                | 0        |
| Com_show_errors                   | 0        |
| Com_show_fields                   | 0        |
| Com_show_grants                   | 0        |
| Com_show_innodb_status            | 0        |
| Com_show_keys                     | 0        |
| Com_show_logs                     | 0        |
| Com_show_master_status            | 0        |
| Com_show_ndb_status               | 0        |
| Com_show_new_master               | 0        |
| Com_show_open_tables              | 0        |
| Com_show_privileges               | 0        |
| Com_show_processlist              | 0        |
| Com_show_slave_hosts              | 0        |
| Com_show_slave_status             | 0        |
| Com_show_status                   | 1        |
| Com_show_storage_engines          | 0        |
| Com_show_tables                   | 0        |
| Com_show_triggers                 | 0        |
| Com_show_variables                | 0        |
| Com_show_warnings                 | 0        |
| Com_slave_start                   | 0        |
| Com_slave_stop                    | 0        |
| Com_stmt_close                    | 0        |
| Com_stmt_execute                  | 0        |
| Com_stmt_fetch                    | 0        |
| Com_stmt_prepare                  | 0        |
| Com_stmt_reset                    | 0        |
| Com_stmt_send_long_data           | 0        |
| Com_truncate                      | 0        |
| Com_unlock_tables                 | 0        |
| Com_update                        | 0        |
| Com_update_multi                  | 0        |
| Com_xa_commit                     | 0        |
| Com_xa_end                        | 0        |
| Com_xa_prepare                    | 0        |
| Com_xa_recover                    | 0        |
| Com_xa_rollback                   | 0        |
| Com_xa_start                      | 0        |
| Compression                       | OFF      |
| Connections                       | 5        |
| Created_tmp_disk_tables           | 0        |
| Created_tmp_files                 | 5        |
| Created_tmp_tables                | 1        |
| Delayed_errors                    | 0        |
| Delayed_insert_threads            | 0        |
| Delayed_writes                    | 0        |
| Flush_commands                    | 1        |
| Handler_commit                    | 0        |
| Handler_delete                    | 0        |
| Handler_discover                  | 0        |
| Handler_prepare                   | 0        |
| Handler_read_first                | 0        |
| Handler_read_key                  | 0        |
| Handler_read_next                 | 0        |
| Handler_read_prev                 | 0        |
| Handler_read_rnd                  | 0        |
| Handler_read_rnd_next             | 0        |
| Handler_rollback                  | 0        |
| Handler_savepoint                 | 0        |
| Handler_savepoint_rollback        | 0        |
| Handler_update                    | 0        |
| Handler_write                     | 130      |
| Innodb_buffer_pool_pages_data     | 19       |
| Innodb_buffer_pool_pages_dirty    | 0        |
| Innodb_buffer_pool_pages_flushed  | 0        |
| Innodb_buffer_pool_pages_free     | 493      |
| Innodb_buffer_pool_pages_latched  | 0        |
| Innodb_buffer_pool_pages_misc     | 0        |
| Innodb_buffer_pool_pages_total    | 512      |
| Innodb_buffer_pool_read_ahead_rnd | 1        |
| Innodb_buffer_pool_read_ahead_seq | 0        |
| Innodb_buffer_pool_read_requests  | 77       |
| Innodb_buffer_pool_reads          | 12       |
| Innodb_buffer_pool_wait_free      | 0        |
| Innodb_buffer_pool_write_requests | 0        |
| Innodb_data_fsyncs                | 3        |
| Innodb_data_pending_fsyncs        | 0        |
| Innodb_data_pending_reads         | 0        |
| Innodb_data_pending_writes        | 0        |
| Innodb_data_read                  | 2494464  |
| Innodb_data_reads                 | 25       |
| Innodb_data_writes                | 3        |
| Innodb_data_written               | 1536     |
| Innodb_dblwr_pages_written        | 0        |
| Innodb_dblwr_writes               | 0        |
| Innodb_log_waits                  | 0        |
| Innodb_log_write_requests         | 0        |
| Innodb_log_writes                 | 1        |
| Innodb_os_log_fsyncs              | 3        |
| Innodb_os_log_pending_fsyncs      | 0        |
| Innodb_os_log_pending_writes      | 0        |
| Innodb_os_log_written             | 512      |
| Innodb_page_size                  | 16384    |
| Innodb_pages_created              | 0        |
| Innodb_pages_read                 | 19       |
| Innodb_pages_written              | 0        |
| Innodb_row_lock_current_waits     | 0        |
| Innodb_row_lock_time              | 0        |
| Innodb_row_lock_time_avg          | 0        |
| Innodb_row_lock_time_max          | 0        |
| Innodb_row_lock_waits             | 0        |
| Innodb_rows_deleted               | 0        |
| Innodb_rows_inserted              | 0        |
| Innodb_rows_read                  | 0        |
| Innodb_rows_updated               | 0        |
| Key_blocks_not_flushed            | 0        |
| Key_blocks_unused                 | 14497    |
| Key_blocks_used                   | 0        |
| Key_read_requests                 | 0        |
| Key_reads                         | 0        |
| Key_write_requests                | 0        |
| Key_writes                        | 0        |
| Last_query_cost                   | 0.000000 |
| Max_used_connections              | 1        |
| Not_flushed_delayed_rows          | 0        |
| Open_files                        | 14       |
| Open_streams                      | 0        |
| Open_tables                       | 6        |
| Opened_tables                     | 0        |
| Qcache_free_blocks                | 0        |
| Qcache_free_memory                | 0        |
| Qcache_hits                       | 0        |
| Qcache_inserts                    | 0        |
| Qcache_lowmem_prunes              | 0        |
| Qcache_not_cached                 | 0        |
| Qcache_queries_in_cache           | 0        |
| Qcache_total_blocks               | 0        |
| Questions                         | 4        |
| Rpl_status                        | NULL     |
| Select_full_join                  | 0        |
| Select_full_range_join            | 0        |
| Select_range                      | 0        |
| Select_range_check                | 0        |
| Select_scan                       | 1        |
| Slave_open_temp_tables            | 0        |
| Slave_retried_transactions        | 0        |
| Slave_running                     | OFF      |
| Slow_launch_threads               | 0        |
| Slow_queries                      | 0        |
| Sort_merge_passes                 | 0        |
| Sort_range                        | 0        |
| Sort_rows                         | 0        |
| Sort_scan                         | 0        |
| Table_locks_immediate             | 12       |
| Table_locks_waited                | 0        |
| Tc_log_max_pages_used             | 0        |
| Tc_log_page_size                  | 0        |
| Tc_log_page_waits                 | 0        |
| Threads_cached                    | 0        |
| Threads_connected                 | 1        |
| Threads_created                   | 4        |
| Threads_running                   | 1        |
| Uptime                            | 780      |
+-----------------------------------+----------+
222 rows in set (0.07 sec)

mysql>
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  4 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

mysql> use library
Database changed
mysql> show processlist;
+----+------+-----------+---------+---------+------+-------+------------------+
| Id | User | Host      | db      | Command | Time | State | Info             |
+----+------+-----------+---------+---------+------+-------+------------------+
|  4 | root | localhost | library | Query   |    0 | NULL  | show processlist |
+----+------+-----------+---------+---------+------+-------+------------------+
1 row in set (0.00 sec)

在大多数情况下，不需要修改mysql的默认配置，但是在默认配置不适宜用户的情况下，mysql提供了大量变量，这些值可以进行修改，以便满足定制要求。有写变量可以在mysql服务器启动时进行定义，还有一些变量是在服务器运行中定义。
使用选项文件来配置mysql：
在mysql选项的推荐方法是通过一个选项文件，在linux环境中，此文件是位于/etc目录下的my.cnf文件。可以要求mysql通过在mysql的命令行上指定带-defaults-file选项的文件名，在不同的位置上查找启动选项。选项文件的格式通常比较简单，类似一个windownINI文件，文件按组来划分，每个组包含可变值对，在mysql命令行中指定的任何选项可以放置在这个文件中，前面不必使用双斜线。
mysql带有的所有二进制程序都可以在一个选项文件中读选项，简单地确定程序名作为选项的一个组（通过在方括号中的名称），并且后面跟希望设置的变量，mysql可户籍程序可以使用一个特殊的client组，通常这个组用于保存用户名和口令连接参数。下面是这个命令工作的简单的示例：
[client]
        user=timethy
        password=greenpeas
        mysql发布中带有很多示例配置文件，可以参考my.large.cnf,my-huge.cnf,my-medium.cnf,my-small.cnf文件。通常mysql需要在组［mysql］和［mysqld］中寻找配置选项。在linux环境中，如果使用mysqld_safe mysql包装来启动mysql，也可以使用[mysqld-safe]组来向mysql服务器传递选项。在这种情况下，mysql壳户机试图连接到mysql服务器时，将可以连接一个口令为greenpass的用户timothy。下表列出了可以用于配置mysql的常用的选项。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
选项   意义
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
ansi   使用更为严格的ansisql-99语法
basedir   设置mysql安装目录的位置
datadir   设置mysql数据目录的位置
debug   创建一个排错文件
default-character-set 设置默认字符集
default-table-type 为新表设置默认表类型
flush   在每个sql命令后向磁盘写变化
init-file  设置包含在启动时执行的sql命令文件
language  设置错误信息的语言
log   向日志写mysql信息（连接和查询）
log-long-format  向每个日志项目添加其他信息(类似用户名和时间戳)
log-error  向日志写关键错误信息
log-warnings  向日志写警告信息
log-slow-queries 向日志写慢执行查询
log-bin   向二进制日志(用于备份和复制)写修改数据的查询
port   设置监听服务器的端口，以便客户机连接
safe-show-database 只想已登陆的用户显示数据库
skip-bdb  禁止bdb表处理
skip-innodb  禁止innodb表处理
skip-grant-tables 执行访问控制时绕过授权表
skip-networking  只允许本地请求，停止监听tcp/ip请求
transaction-isolation 设置默认事务隔离等级
user   确定可以作为用户运行的服务器
tmpdir   设置临时文件区的位置
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
所有这些选项都可以在mysql命令行中指定，示例如下：
#/usr/local/mysql/bin/mysqld_safe --socket=/usr/tmp/mysql.socket --user=mysql --skip-networking &
当存在多种选项文件或相同选项指定了多次不同值时，mysql使用最后发现的值，因为mysql在命令行变量之前读选项文件，这意味着命令行指定的选项权限优先于选项文件中的选项。
mysql服务器处于运行状态时，可以使用set命令修改系统和连接变量，命令格式为：
set variable=value,variable=value,…
示例：
mysql> set table_type=innodb;
        Query OK, 0 rows affected, 1 warning (0.00 sec)
     使用set命令的变量集，根据set关键字后面使用的是global或session关键字，可以全局设置在所有会话上，或者只设置在当前会话上，下面的示例限制服务器在任何时间内的连接１０个客户机，并设置读缓存大小为２５０kb：
mysql> set global max_user_connections=10,session read_buffer_size=250000;
Query OK, 0 rows affected (0.01 sec)
如果应用服务器与mysql服务器是同一个服务器，同时不希望mysql客户机可以从其他主机上连接，可以使用--skip-networking选项来关闭对tcp/ip的监听来提高mysql的安全性。

            set命令的重要变量
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
变量   功能
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
autocommit  切换mysql是否试图自动提交打开事务
key_buffer_size  设置索引使用的缓冲大小
table_cache  设置mysql可以在任何时间内打开的表的总数
concurrent_inserts 在myISAM表中允许inser和select并发
table_type  设置默认表的类型
interactive_timeout 设置对话客户机连接的时限
language  设置错误信息使用的语言
sort_buffer_size 为读表设置缓冲规模
max_binlog_size  设置循环之前二进制日志的最大规模
max_connections  设置在任意具体时刻允许客户机连接的最大数量
max_user_connections 设置在任意时刻可激活的单独客户机连接的最大数量
max_tmp_tables  设置任何具体时刻客户机可以打开临时表的最大数量
query_cache_type 查询高速缓存开关切换
query_cache_size 设置高速缓存的最大数量
tmpdir   设置临时文件区的位置
tx_isolation  设置事务独立等级
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
检索变量值
变量一旦已经通过set或启动选项设置，可以使用showvsriables命令来查看启动项。showvariables的输出结果很大，可以和like语句组合使用。
mysql> show variables like 'table_type';l
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| table_type    | InnoDB |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show variables like '%innodb%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| have_innodb                     | YES                    |
| innodb_additional_mem_pool_size | 1048576                |
| innodb_autoextend_increment     | 8                      |
| innodb_buffer_pool_awe_mem_mb   | 0                      |
| innodb_buffer_pool_size         | 8388608                |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_io_threads          | 4                      |
| innodb_file_per_table           | OFF                    |
| innodb_flush_log_at_trx_commit  | 1                      |
| innodb_flush_method             |                        |
| innodb_force_recovery           | 0                      |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_arch_dir             |                        |
| innodb_log_archive              | OFF                    |
| innodb_log_buffer_size          | 1048576                |
| innodb_log_file_size            | 5242880                |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 90                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_open_files               | 300                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 20                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 8                      |
| innodb_thread_sleep_delay       | 10000                  |
+---------------------------------+------------------------+
34 rows in set (0.01 sec)
也可以通过select@@variable的格式来查询系统变量的值：
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.01 sec)