Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to back up a my.cnf configuration file by Mysql". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "how to back up a my.cnf configuration file by Mysql"!

Record a my.cnf in case you need it from time to time

[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

Table_open_cache_instances = 128,

Innodb_online_alter_log_max_size=1G

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

Remarks: about replicate-wild-do-table

1 if you use replicate-wild-do-table replicate-wild-ignore-table at the same time

Replicate-wild-do-table=test.%

Replicate-wild-ignore-table=test2.%

So: only the operations of test on the master library will be synchronized to the slave library, test2 and other libraries will not be synchronized, and the new db will not be synchronized.

two。 Only replicate-wild-ignore-table.

Replicate-wild-ignore-table= liuliang.%

So: only the operation of the liuliang library on the main library is out of sync, everything else is synchronized, and the new library will also be synchronized!

3. Only replicate-wild-do-table.

Replicate-wild-do-table=oauth.%

So: only the operation of oauth on the main library is synchronized, nothing else is synchronized, and the newly created db will not be synchronized = equal to the first mode!

Back up another one

[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

Slow_query_log = 1

Pid-file = / data/3306/mysql.pid

Log-bin = / data/3306/binlog/mysql-bin

Binlog_format=ROW

Slave-skip-errors = 1032 and 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-- enable event event support

Long_query_time = 1

Max_binlog_size = 256m

Binlog_cache_size = 8m

Expire_logs_days = 7

Relay_log_purge = 0-relay lof automatically clears off. When enabled, it can be cleared manually through flush logs.

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-maximum value of temporary table in memory

Max_heap_table_size = 64m

Bulk_insert_buffer_size = 32m-bulk insert temporary memory

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-context switch spin lock

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

At this point, I believe you have a deeper understanding of "how Mysql backs up a my.cnf configuration file". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.