Mysql怎么備份一個(gè)my.cnf配置文件

本篇內(nèi)容主要講解“Mysql怎么備份一個(gè)my.cnf配置文件”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“Mysql怎么備份一個(gè)my.cnf配置文件”吧!

記錄一個(gè)my.cnf吧 以免不時(shí)之需

1. [client]
   port = 3307
   socket = /home/data/mydata/3307/mysql.sock
   
   [mysqld]
   port = 3307
   socket = /home/data/mydata/3307/mysql.sock
   
   basedir = /home/data/mysql
   datadir = /home/data/mydata/3307
   pid-file = /home/data/mydata/3307/mysql.pid
   server-id = 0013307
   
   skip-name-resolve
   back_log = 600
   ft_min_word_len          = 1
   max_connections = 8096
   max_connect_errors = 99999999
   #open_files_limit = 65535
   table_open_cache = 2048 
   max_allowed_packet = 128M
   binlog_cache_size = 8M
   max_heap_table_size = 128M
   tmp_table_size = 128M
   read_buffer_size = 16M
   read_rnd_buffer_size = 32M
   sort_buffer_size = 8M
   join_buffer_size = 8M
   key_buffer_size = 128M
   thread_cache_size = 8
   query_cache_size = 0
   query_cache_limit = 2M

2. table_open_cache_instances = 128

3. innodb_online_alter_log_max_size=1G

4. innodb_io_capacity=2000
   
   log-bin = mysql-bin
   binlog_format = ROW
   replicate-wild-do-table=test.%
   replicate-wild-do-table=mysql.%
   replicate-wild-do-table= information_schema.%
   replicate-wild-do-table=interface_hd_com.%
   replicate-wild-ignore-table=test2.%
   replicate-wild-ignore-table= performance_schema.%
   log_slave_updates
   relay-log=mysql-relay-bin
   skip_slave_start
   
   
   gtid_mode=ON
   enforce-gtid-consistency
   report-host=192.168.1.157
   report-port=3307
   master-info-repository=TABLE
   relay-log-info-repository=TABLE
   #sync-master-info = 1
   
   
   expire_logs_days = 3
   log_error = /home/data/mydata/3307/mysql-error.log
   slow_query_log = 1
   long_query_time = 1
   slow_query_log_file = /home/data/mydata/3307/mysql-slow.log
   performance_schema = 0
   skip-external-locking
   
   default_storage_engine = InnoDB
   innodb_file_per_table = 1
   innodb_open_files = 1500
   innodb_buffer_pool_size = 10240M
   innodb_write_io_threads = 4
   innodb_read_io_threads = 4
   innodb_thread_concurrency = 0
   innodb_purge_threads = 1
   innodb_flush_log_at_trx_commit = 1
   innodb_log_buffer_size = 2M
   innodb_log_file_size = 32M
   innodb_log_files_in_group = 3
   innodb_max_dirty_pages_pct = 90
   innodb_lock_wait_timeout = 120
   
   #innodb_undo_tablespaces = 16
   #innodb_undo_directory = /home/data/mydata/3307/undo
   
   bulk_insert_buffer_size = 8M
   myisam_sort_buffer_size = 8M
   myisam_max_sort_file_size = 10G
   myisam_repair_threads = 1
   
   interactive_timeout = 28800
   wait_timeout = 28800
   
   [mysqldump]
   quick
   max_allowed_packet = 16M
   [myisamchk]
   key_buffer_size = 8M
   sort_buffer_size = 8M
   read_buffer = 4M
   write_buffer = 4M
   

備注：關(guān)于replicate-wild-do-table

1. 1 如果同時(shí)使用replicate-wild-do-table  replicate-wild-ignore-table

1. replicate-wild-do-table=test.%
   replicate-wild-ignore-table=test2.%
   那么：主庫(kù)上的只有test的操作會(huì)同步至從庫(kù)，test2和其它的庫(kù)都不會(huì)同步，新建db 也不會(huì)同步

 2.只有replicate-wild-ignore-table 

 replicate-wild-ignore-table= liuliang.%
  那么：主庫(kù)上只有l(wèi)iuliang庫(kù)的操作不同步，其它的都同步，并且新建庫(kù)也會(huì)同步！
 

 3.只有replicate-wild-do-table

 replicate-wild-do-table=oauth.%
那么：主庫(kù)上只有oauth的操作才同步，其它的都不同步，新建的db也不會(huì)同步     ===等同于第一種模式！

再備份一個(gè)

1. [client]
   port    = 3306
   socket  = /data/3306/mysql.sock
   default-character-set=utf8mb4
   
   
   [mysql]
   no-auto-rehash
   default-character-set=utf8mb4
   
   
   [mysqld]
   init-connect='SET NAMES utf8mb4'
   character-set-server = utf8mb4
   skip-character-set-client-handshake 
   lower_case_table_names = 1
   federated
   secure_file_priv=''
   
   server-id=139
   port    = 3306
   socket  = /data/3306/mysql.sock
   basedir = /usr/local/mysql
   datadir = /data/3306/data
   transaction_isolation = REPEATABLE-READ
   log-error = /data/3306/error.log
   slow_query_log_file=/data/3306/slow-log.log

2. slow_query_log = 1
   pid-file = /data/3306/mysql.pid
   log-bin = /data/3306/binlog/mysql-bin
   binlog_format=ROW
   slave-skip-errors = 1032,1062
   
   skip-external-locking
   skip-name-resolve
   default-storage-engine = INNODB
   wait_timeout=200
   connect_timeout = 20
   interactive_timeout=200
   back_log = 500    
   event_scheduler = ON   --開(kāi)啟event事件支持
   long_query_time = 1
   max_binlog_size = 256M
   binlog_cache_size = 8M
   expire_logs_days = 7
   relay_log_purge = 0      ----relay lof 自動(dòng)清除關(guān)閉，開(kāi)啟后可通過(guò)flush logs手動(dòng)清除
   relay_log_recovery = 1
   skip-slave-start = 1
   log_slave_updates = 1
   max_connections = 3000
   table_open_cache = 6144
   table_definition_cache = 4096
   sort_buffer_size = 512K
   read_buffer_size = 512K
   read_rnd_buffer_size = 512K
   tmp_table_size = 64M    --內(nèi)存臨時(shí)表的最大值
   max_heap_table_size = 64M  
   bulk_insert_buffer_size = 32M   -- 批量插入暫存使用內(nèi)存
   thread_cache_size = 8M
   thread_stack = 256K
   max_allowed_packet = 500M
   
   join_buffer_size = 4M
   innodb_buffer_pool_size = 20G
   innodb_buffer_pool_instances = 4
   innodb_additional_mem_pool_size = 16M
   innodb_log_file_size = 1024M
   innodb_log_buffer_size = 64M
   innodb_log_files_in_group = 3
   innodb_flush_log_at_trx_commit = 1
   innodb_sync_spin_loops = 40    --上下文切換自旋鎖
   innodb_max_dirty_pages_pct = 90
   innodb_support_xa = 1
   innodb_thread_concurrency = 0
   innodb_thread_sleep_delay = 500
   innodb_file_io_threads = 4
   innodb_lock_wait_timeout = 200
   innodb_concurrency_tickets = 1000
   log_bin_trust_function_creators = 1
   innodb_flush_method = O_DIRECT
   innodb_file_per_table = 1
   innodb_read_io_threads = 8
   innodb_write_io_threads = 8
   innodb_io_capacity = 1500
   innodb_file_format = Barracuda
   innodb_purge_threads = 4
   innodb_purge_batch_size = 32
   innodb_old_blocks_pct=75
   innodb_change_buffering = all
   group_concat_max_len=102400
   loose_max_statement_time=600000
   innodb_print_all_deadlocks = 1
   
   [mysqldump]
   quick
   max_allowed_packet = 500M
   
   [mysqld_safe]
   log-error=/data/3306/mysql.err
   pid-file=/data/3306/mysqld.pid
   
   

到此，相信大家對(duì)“Mysql怎么備份一個(gè)my.cnf配置文件”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢(xún)，關(guān)注我們，繼續(xù)學(xué)習(xí)！