mysql随笔

查看mysql系统变量及值：

mysql> show variables;
+---------------------------------+------------------------------------------+
| Variable_name                   | Value                                    |
+---------------------------------+------------------------------------------+
| auto_increment_increment        | 1                                        |
| auto_increment_offset           | 1                                        |
| automatic_sp_privileges         | ON                                       |
| back_log                        | 50                                       |
| basedir                         | /                                        |
| binlog_cache_size               | 32768                                    |
| bulk_insert_buffer_size         | 8388608                                  |
| character_set_client            | latin1                                   |
| character_set_connection        | latin1                                   |
| character_set_database          | latin1                                   |
| character_set_filesystem        | binary                                   |
| character_set_results           | latin1                                   |
| character_set_server            | latin1                                   |
| character_set_system            | utf8                                     |
| character_sets_dir              | /usr/share/mysql/charsets/               |
| collation_connection            | latin1_swedish_ci                        |
| collation_database              | latin1_swedish_ci                        |
| collation_server                | latin1_swedish_ci                        |
| completion_type                 | 0                                        |
| concurrent_insert               | 1                                        |
| connect_timeout                 | 5                                        |
| datadir                         | /var/lib/mysql/                          |
| date_format                     | %Y-%m-%d                                 |
| datetime_format                 | %Y-%m-%d %H:%i:%s                        |
| default_week_format             | 0                                        |
| delay_key_write                 | ON                                       |
| delayed_insert_limit            | 100                                      |
| delayed_insert_timeout          | 300                                      |
| delayed_queue_size              | 1000                                     |
| div_precision_increment         | 4                                        |
| engine_condition_pushdown       | OFF                                      |
| expire_logs_days                | 0                                        |
| flush                           | OFF                                      |
| flush_time                      | 0                                        |
| ft_boolean_syntax               | + -><()~*:""&|                           |
| ft_max_word_len                 | 84                                       |
| ft_min_word_len                 | 4                                        |
| ft_query_expansion_limit        | 20                                       |
| ft_stopword_file                | (built-in)                               |
| group_concat_max_len            | 1024                                     |
| have_archive                    | YES                                      |
| have_bdb                        | NO                                       |
| have_blackhole_engine           | NO                                       |
| have_compress                   | YES                                      |
| have_crypt                      | YES                                      |
| skip_external_locking           | ON                                       |
| skip_networking                 | OFF                                      |
| skip_show_database              | OFF                                      |
| slave_compressed_protocol       | OFF                                      |
| slave_load_tmpdir               | /tmp/                                    |
| slave_net_timeout               | 3600                                     |
| slave_skip_errors               | OFF                                      |
| slave_transaction_retries       | 10                                       |
| slow_launch_time                | 2                                        |
| socket                          | /var/lib/mysql/mysql.sock                |
| sort_buffer_size                | 524280                                   |
| sync_frm                        | ON                                       |
| system_time_zone                | CST                                      |
| table_cache                     | 64                                       |
| table_lock_wait_timeout         | 50                                       |
| table_type                      | MyISAM                                   |
| thread_cache_size               | 0                                        |
| thread_stack                    | 126976                                   |
| time_format                     | %H:%i:%s                                 |
| time_zone                       | SYSTEM                                   |
| timed_mutexes                   | OFF                                      |
| tmp_table_size                  | 33554432                                 |
| tmpdir                          | /tmp/                                    |
| transaction_alloc_block_size    | 8192                                     |
| transaction_prealloc_size       | 4096                                     |
| tx_isolation                    | REPEATABLE-READ                          |
| updatable_views_with_limit      | YES                                      |
| version                         | 5.0.37-standard-log                      |
| version_comment                 | MySQL Community Edition - Standard (GPL) |
| version_compile_machine         | i686                                     |
| version_compile_os              | pc-linux-gnu                             |
| wait_timeout                    | 28800                                    |
+---------------------------------+------------------------------------------+
222 rows in set (0.00 sec)

更新auto_increment_increment的初始值：

mysql> set @auto_increment_increment=10;
Query OK, 0 rows affected (0.01 sec)

再次查看auto_increment_increment：

mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysqldump备份：

Linux:

mysqldump -u root -pmaksim db_name>/home/soft/test.sql;

Windows:

mysqldump -u root -pmaksim db_name>C:\soft\test.sql;