Difference between revisions of "Mysql Notes"

From Urandom
Jump to: navigation, search
Line 8: Line 8:
 
auto_increment_offset=X # where X is the server number (e.g 1,2,3...)
 
auto_increment_offset=X # where X is the server number (e.g 1,2,3...)
 
server-id      = Z # where Z is the server number (e.g 1,2,3...)
 
server-id      = Z # where Z is the server number (e.g 1,2,3...)
 +
#In mysql 5.6 and later dont use the master-* flags.
 
master-host    =  master.fleet.zone # set to the master hostname
 
master-host    =  master.fleet.zone # set to the master hostname
 
master-user    =  Username #replication user
 
master-user    =  Username #replication user
Line 59: Line 60:
 
<pre>
 
<pre>
 
stop slave;
 
stop slave;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=106;
+
CHANGE MASTER TO MASTER_HOST = '3.3.3.3', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
 
SLAVE START;
 
SLAVE START;
 
</pre>
 
</pre>

Revision as of 08:39, 16 June 2015

Just some mysql notes i find useful

Mysql Replication related

Master-Master or Master-Slave configuration

/etc/my.conf

auto_increment_increment=Y # where Y is the total amount of servers
auto_increment_offset=X # where X is the server number (e.g 1,2,3...)
server-id       = Z # where Z is the server number (e.g 1,2,3...)
#In mysql 5.6 and later dont use the master-* flags. 
master-host     =   master.fleet.zone # set to the master hostname
master-user     =   Username #replication user
master-password =   Password #replication password
master-port     =  3306
log-bin=mysql-bin
binlog_format=mixed
binlog-ignore-db=db1 #skip replication db (master, these wont be replicated to slaves)
binlog-ignore-db=db2
binlog-ignore-db=dbN
relay-log=slavehostname-relay-bin
expire_logs_days=14 #this is how long the bin-log is kept

#not mandatory, but use utf8
default-character-set=utf8
default-collation=utf8_unicode_ci
character-set-server=utf8
collation-server=utf8_unicode_ci
init-connect='SET NAMES utf8'

To add replication users use

grant replication slave on *.* to 'replication'@SLAVEHOST identified by 'Password';

To add replication client for monitoring

grant replication client on *.* to 'replication'@ACCESSHOST identified by 'Password';
  • enables to use show slave/master status command.

Cluster synchronization

Start the slave from replication or stop it.

stop slave; 
start slave;

Skiping slave replication errors

To skip errors in replication use

stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;
  • You can replace 1 with a larger number, but then be sure to run
stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 0; start slave;
  • Then you wont skip errorors later on and they go un noticed.

Change slave position

If for some reason the slave replicates the master from an unwanted position, you can change the current position. This might be acceptable approach for a master-to-master replication.

stop slave;
CHANGE MASTER TO MASTER_HOST = '3.3.3.3', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
SLAVE START;

Live clone new slave or restore slave from scrach

Reset Master log

  • We do this so replication will start from a new point
RESET MASTER;

Dump the master database

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

Database recovery

Fix broken or corrupt tables

mysqlcheck -u root -p --auto-repair -c --all-databases
* Can also be combined with -o as in optimize.

Check for broken or corrupt tables

mysqlcheck -c -u root -p --all-databases

Converting databases

Convert all tables to UTF-8

read -p"What is the mysql username?: " USER
read -p"What is the mysql password?: " PASS
read -p"What is the mysql database to convert?: " DATAB
mysql -u $USER -p$PASS --database=$DATAB -B -N -e "SHOW TABLES" | awk '{print "ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"}' | mysql-u $USER -p$PASS --database=$DATAB

Convert all tables to InnoDB

read -p"What is the mysql username?: " USER
read -p"What is the mysql password?: " PASS
read -p"What is the mysql database to convert?: " DATAB
mysql -u $USER -p$PASS --database=$DATAB -B -N -e "SHOW TABLES" | awk '{print "ALTER TABLE", $1, "ENGINE = InnoDB;"}' | mysql-u $USER -p$PASS --database=$DATAB