MySQL master and slave configuration parameters Daquan

Master All Parameters
1 Log-bin=mysql-bin
1, control whether the master is open Binlog record function;
2, binary files preferably placed in a separate directory, which not only convenient optimization, more convenient maintenance.
3, renaming the binary log is very simple, only need to modify the [mysqld] in the Log_bin option,
The following example: to readjust the logbin path to "/home/mysql/binlog"
[Mysqld]
Log_bin=/home/mysql/binlog/binlog.log

Ll/home/mysql/binlog
-RW-RW----1 MySQL mysql 98 Mar 7 17:24 binlog.000001
-RW-RW----1 MySQL mysql 7 17:24 binlog.index

Note: When specifying a directory, you must end with *.log, that is, you cannot specify only the level of the folder, or you will get an error when restarting MySQL.

2. server-id=1
The identity of each server service, in a master/slave environment, this variable must be the same

3. expire_logs_days=15
This allows master to automatically delete Binlog

4. Innodb_flush_log_at_trx_commit=1
This parameter represents the way the redo log is handled when the transaction commits; This variable has three optional values 0,1,2:
0: When a transaction commits, it does not write the redo log of the transaction to the log file, but waits for a refresh once per second
1: When a transaction commits, the contents of the Redo log cache are synchronously written to the disk log file, and this value is used in the replication environment in order to ensure data consistency.
2: Asynchronously writes the contents of the Redo log cache to the disk log file (written to the file system cache) when the transaction commits
It is recommended that you set innodb_flush_log_at_trx_commit=1

5.sync_binlog=1
1. This parameter indicates how many times each write buffer is synchronized to disk;
2. sync_binlog=1 indicates synchronous write buffer and disk binary log file, not using File system cache
When using the InnoDB transaction engine, it is set to "1" in the replication environment to ensure maximum availability, but will affect the performance of IO.
3, even if set to "1", there will be problems occur:
If the binary log is written to disk, but the transaction has not yet been commit, this time the outage,
When the service comes back up again, it cannot rollback and log uncommitted content to the binary log;
This will cause the master and slave data to be inconsistent.
Solution:
Parameter innodb_support_xa=1 is required to guarantee. It is recommended that you set

6. Innodb_support_xa=1
This parameter is related to XA transactions, which guarantee the synchronization of binary logs and InnoDB data files to ensure data consistency in the replication environment. It is recommended that you set

7.binlog-do-db=skate_db
Only records updates from the specified database into the binary log

8. Binlog-do-table=skate_tab
Only records updates from the specified table to the binary log

9. binlog-ignore-db=skate_db
Ignoring updates to the specified database into the binary log

10.log_slave_updates=1
This parameter controls whether the slave database logs the log received from master and the contents of this slave to the slave binary log.
This parameter is required in a cascading replication environment (including dual-master environments)

11.binlog_format=statement|row|mixed
Controls what format the contents of the binary log are recorded by default mixed

Max_binlog_size.
The size of each binary log file for master, default 1G

13.binlog_cache_size
1, all uncommitted transactions will be recorded in a cache or temporary files, to be submitted, unified synchronization into the binary log,
2, this variable is session-based, each session opens a binlog_cache_size-size cache.
3, the variable "binlog_cache_disk_use" and "Binlog_cache_use" to set the size of the binlog_cache_size.
Description
Binlog_cache_disk_use: The number of times a binary log is written using a temporary file
Binlog_cache_use: The number of times the binary is written using buffering

14.auto_increment_increment=2//Growth step
Auto_increment_offset=1//Start position
Prevents key-value collisions in a dual-master environment

parameters used by the slave
1.server-id=2
Like the meaning of master, the service identity

2.log-bin=mysql-bin
As with master, turn on the binary

3.relay-log=relay-bin
Path name of the trunk log file

4. Relay-log-index=relay-bin
Path name of the trunk log index file

5. Log_slave_updates=1
As with master, the above

6.read_only=1
1. Make the database read-only, this parameter does not work in slave replication environment and users with super privileges.
2, for the replication environment settings Read_only=1 is very useful, it can ensure that slave only accept the Master Update, but not accept the client update.
3. Client settings:
Mysq> Set Global Read_only=1

7. Skip_slave_start
So that slave does not start the replication process when MySQL starts, and after MySQL is started using start slave, it is recommended that you

8.replicate-do-db
Copy only specified db

9.replicate-do-table
Copy only the specified table

Ten. Replicate-ingore-table
Ignore specified table

replicate_wild_do_table=skatedb.%.
Fuzzy match replication Specifies DB

auto_increment_increment=2.
Auto_increment_offset=1
As with the master meaning, refer to the above

Log_slow_slave_statements.
Open the slow query log on slave, when the query time is greater than Long_query_time, recorded in the slow query log

Max_relay_log_size.
The size of the relay log on the slave, the default is 1G

15.relay_log_info_file
Path name of the relay log status information file

Relay_log_purge.
deleted when relay log is not required, default is on
SET GLOBAL relay_log_purge=1

17.replicate-rewrite-db=from_name->to_name
Database redirection, you can summarize the library to the main library to facilitate statistical analysis

MySQL master and slave configuration parameters Daquan