MySQL: Set Server Variables

There are more than one way to set or change paramenter values.

1.Set my.cnf file.

Parameter values are read into memory during server start up.

Parameter is entered in the form of key value pair. All the parameter should enter in the my,cnf file so that the server will apply the parameter values in start up. After any change to parameter values using Set Global option, values should also change in the parameter file.

The syntax of the paramer key value paire is:

parameter=value
key_buffer_size=1073741824;

To translate GB, MB, KG to byte, use the following formula:
1GB = 1*1024*1024*1024
1MB = 1*1024*1024
1KB = 1*1024
1.5GB = 1.5*1024*1024*1024

2.Enter in command line or in the start up script

You may find this is not as convince as using the configuration file.

shell> mysqld --key_buffer_size=64M --table_cache=256 \
--sort_buffer_size=4M --read_buffer_size=1M &

3.Use Set GLOBAL command for Dynamic Variable from mysql client

If the parameter is a dynamic varable, we can use MYSQL client to change the value in run time.

Mysql> SET GLOBAL key_buffer_size=1073741824;

To translate values to bytes, you can use the following formula to enter GB, MB, or KB.

1GB = 1*1024*1024*1024
1MB = 1*1024*1024
1KB = 1*1024
1.5GB = 1.5*1024*1024*1024

MYSQL> SET GLOBAL key_buffer_size=1*1024*1024*1024;

The parameter values are load when the sessions started. If we want to use the new values that we just changed using the SET GLOBAL command, we need to start a new session.

MySql Replication: Skip Duplicate Key Error (Error 1062, Unique Constraint Violation)

MySQL replication will stop whenever there is a query causing an error on the slave. The error message will be written to the error log; therefore, it allows us to identify the problem and fix it,

One of the common error is error 1062, unique constraint violation, that is, when a duplicate key is being insert into a table. For example you might see an errors message in the error log or from “show slave status” output:

"1062 Error 'Duplicate entry 'abc' for key 1' on query. Default database: 'mydb'. Query: 'INSERT INTO ..."

Since, in most case, this error will need to fix in the source (master) database, we can skip the error and let the replication continue on slave.

To skip an error , from the slave, you can use the SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1 statement to skip a sql statement which caused the 1062 error.

mysql>stop slave;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

For, 1062 error, during the normal replication process, you can also specify the following variable in the
my.cnf configuration file to tell the slave to skip all 1062 errors

slave-skip-errors = 1062

Of cause, please make sure that all the statements, which were skipped, will eventually be corrected in the master and will not cause any data issue.

Mysql Replication: Use Slave Skip Counter to Skip Error Statement

To Skip an error in a replication event during replication, use:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N

This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement.

This statement is valid only when the slave thread is not running. Otherwise, it produces an error.

When using this statement, it is important to understand that the binary log is actually organized as a sequence of groups known as event groups. Each event group consists of a sequence of events.

· For transactional tables, an event group corresponds to a transaction.
· For nontransactional tables, an event group corresponds to a single SQL statement.

A single transaction can contain changes to both transactional and nontransactional tables.

When you use SET GLOBAL SQL_SLAVE_SKIP_COUNTER to skip events and the result is in the middle of a group, the slave continues to skip events until it reaches the end of the group. Execution then starts with the next event group.

Examples of using SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N :

1. Skip statements that caused duplicates or referential integrity error.

When a referential integrity error occurred, the transaction will cause the replication thread to hang. To skip the transaction do:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;

Use the value 1 for any SQL statement that does not use AUTO_INCREMENT or LAST_INSERT_ID(), otherwise you will need to use the value 2. Statements that use AUTO_INCREMENT or LAST_INSERT_ID() take up 2 events in the binary log.