MAC OS X, Linux, Windows and other IT Tips and Tricks

03 Jan 13 Mysql Loop replicating in Debian Squeeze

I have been doing replicating from master to Slave for quite a while and sometimes found it a bit limited especially when the master in down and ‘writes’ need to be done to the databases. Here is a method where no matter which server is down you can always write to it and the changes will get replicated to the other one. Even if the other server is down, the changes will get synchronized automatically when it comes back up.
Note: This method only works between 2 MySQL servers where each server is the master and the slave of the other.
This method has proven very stable no matter which server is brought down and back up, the replication has always worked well.
Very useful to make backups of all databases on both servers without any service downtime.

Here I will name the 2 servers: A-server( and B-server(
Install standard Mysql server in both servers:
apt-get install mysql-server mysql-client
Create a replication user replicant and add a password for it.
Make sure the name given (here is replicant given)and passwords are the same on both servers.
This will be used to allow loop replication between the 2 servers.
In those commands below only the replication privileges are given to the replication user.
mysql -uroot -p
(enter the MySQL root pawssowrd)
mysql> CREATE USER 'replicant'@'%' IDENTIFIED BY 'ChangeMe';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'%';
mysql> GRANT REPLICATION CLIENT ON *.* TO 'replicant'@'%';
mysql> GRANT SUPER ON *.* TO 'replicant'@'%';
mysql> quit;

In A-server:/etc/mysql/my.cnf
After the ‘Login and Replication’ section add the following lines:

#------------------------------- MASTER area ---------
server-id = 84
# ------------------------------ SLAVE area ----------
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/
relay-log-index = /var/log/mysql/relay-log.index
# ----------------------------------------------------
log-bin = /var/log/mysql/mysql-bin.log
log-error = /var/log/mysql/mysql-bin.err
log-bin-index = /var/log/mysql/log-bin.index

In B-server:/etc/mysql/my.cnf
After the ‘Login and Replication’ section add the following lines:

#------------------------------- MASTER area ---------
server-id = 85
# ------------------------------ SLAVE area ----------
# Preventing Replica-stop when duplicates are replicated
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/
relay-log-index = /var/log/mysql/relay-log.index
# ----------------------------------------------------
log-bin = /var/log/mysql/mysql-bin.log
log-error = /var/log/mysql/mysql-bin.err
log-bin-index = /var/log/mysql/log-bin.index

Start both MySql servers and issue the following command to see if the replication is already in sync.
It might be helpful beforehand to issue a few SQL writes commands to one of the servers to see if the replication does work.

echo "SHOW SLAVE STATUS\G;" | mysql --host= --port=3306 -u replicant --password=ChangeMe | egrep "Slave_IO_State:" | head -n 1 | cut -d: -f2
echo "SHOW SLAVE STATUS\G;" | mysql --host= --port=3306 -u replicant --password=ChangeMe | egrep "Slave_IO_State:" | head -n 1 | cut -d: -f2

If all is good then both results should be: Waiting for master to send event
If you want to see the whole information regarding the replication, issue the following command:
echo "SHOW SLAVE STATUS\G;" | mysql --host= --port=3306 -u replicant --password=ChangeMe
echo "SHOW SLAVE STATUS\G;" | mysql --host= --port=3306 -u replicant --password=ChangeMe

The replication is doing good when the following information in the results is found:
Waiting for master to send event
Read_Master_Log_Pos should be the same value as for Exec_Master_Log_Pos
Read_Master_Log_Pos: 106
Exec_Master_Log_Pos: 106

That should imply as well the following result:
Seconds_Behind_Master: 0

20 Aug 12 Flushing mySQL Binary logs

In order to keep the disk usage of the binlogs down while having replication, it is necessary to periodically perform a PURGE BINARY LOGS command.

Please keep in mind that it is important to ensure that you don’t remove any binlogs that are still needed by the replicas.

For example:
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

You can read more about this command here:

09 Oct 10 Running a second instance of mysqld

Sometimes it’s needed to have 2 instances of mysql daemon in the same server.
In that case I decided to use the port 3307 for the second instance.
Although there are other (maybe even easier) ways to do that, here is one of them.

Creating a second instance of MySQL server on port 3307.

Create a copy of the admin config file /etc/mysql/debian.cnf to /etc/mysql/debian2.cnf with the following content example:
cp /etc/mysql/debian.cnf /etc/mysql/debian2.cnf
vim /etc/mysql/debian2.cnf

# Automatically generated for Debian scripts. DO NOT TOUCH!
host = localhost
user = debian-sys-maint
password = 1cddmCxKskBGzzA6
socket = /var/run/mysqld/mysqld2.sock
user = debian-sys-maint
password = 1cddmCxKskBGzzA6
socket = /var/run/mysqld/mysqld2.sock
basedir = /usr

Create a copy mysql main config file /etc/mysql/my.cnf to /etc/mysql/my2.cnf and change the port, socket, daemon etc. as follows:
cp /etc/mysql/my.cnf /etc/mysql/my2.cnf
vim /etc/mysql/my2.cnf

port = 3307
socket = /var/run/mysqld/mysqld2.sock
# * Basic Settings
user = mysql
pid-file = /var/run/mysqld/
socket = /var/run/mysqld/mysqld2.sock
port = 3307
basedir = /usr
datadir = /var/lib/mysql2
# .....................
master-host =
master-port = 3306
master-user = replicant
master-password = 'xxxxxx'
relay-log = /var/log/mysql/relay2.log
relay-log-info-file = /var/log/mysql/
relay-log-index = /var/log/mysql/relay-log2.index
#Disable the binary logs for replicate
#log_bin = /var/log/mysql/mysql-bin.log
#log-bin-index = /var/log/mysql/log-bin.index
# Disable the master error behavior
log-error = /var/log/mysql/error2.log
log = /var/log/mysql/mysql2.log

the rest can stay the same.

Create a copy of the file /etc/mysql/debian-start to /etc/mysql/debian-start2 with the following content differences:
cp /etc/mysql/debian-start /etc/mysql/debian-start2
vim /etc/mysql/debian-start2

MYSQL="/usr/bin/mysql --defaults-file=/etc/mysql/debian2.cnf"
MYADMIN="/usr/bin/mysqladmin --port=3307 --defaults-file=/etc/mysql/debian2.cnf"
MYUPGRADE="/usr/bin/mysql_upgrade --port=3307 --defaults-extra-file=/etc/mysql/debian2.cnf"
MYCHECK="/usr/bin/mysqlcheck --port=3307 --defaults-file=/etc/mysql/debian2.cnf"
MYCHECK_SUBJECT="WARNING: mysqlcheck has found corrupt tables"
MYCHECK_PARAMS="--all-databases --fast --silent"

Create a copy of the MySQL init script /etc/init.d/mysql to /etc/init.d/mysql2 and modify the following at the beginning of the file:
cp /etc/init.d/mysql /etc/init.d/mysql2
vim /etc/init.d/mysql2

# Modified by michel to use another defauls file (/etc/mysql/debian2.cnf --port=3307)
MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian2.cnf --port=3307"
# priority can be overriden and "-s" adds output to stderr
# Modified by michel: /etc/init.d/mysql2 -i
ERR_LOGGER="logger -p daemon.err -t /etc/init.d/mysql2 -i"
# Usage: void mysqld_get_param option
# Modified by michel to use the config file of second instance of mysql (/etc/mysql/my2.cnf)
mysqld_get_param() {
     /usr/sbin/mysqld --defaults-file=/etc/mysql/my2.cnf --print-defaults \
          | tr " " "\n" \
          | grep -- "--$1" \
          | tail -n 1 \
          | cut -d= -f2
## Do some sanity checks before even trying to start mysqld.
sanity_checks() {
# check for config file
# Modified by michel : /etc/mysql/my2.cnf
   if [ ! -r /etc/mysql/my2.cnf ]; then
case "${1:-''}" in
     # Start daemon
     log_daemon_msg "Starting MySQL database server" "mysqld2"
     if mysqld_status check_alive nowarn; then
          log_progress_msg "already running"
          log_end_msg 0
          /usr/bin/mysqld_safe --defaults-file=/etc/mysql/my2.cnf --mysqld=mysqld2 &
          # 6s was reported in #352070 to be too few when using ndbcluster
          for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do
               sleep 1
               if mysqld_status check_alive nowarn ; then break; fi
               log_progress_msg "."
          if mysqld_status check_alive warn; then
               log_end_msg 0
               # Now start mysqlcheck or whatever the admin wants.
               [ -n "$output" ] && log_action_msg "$output"
               log_end_msg 1
               log_failure_msg "Please take a look at the syslog"

Create a symlink from /usr/sbin/mysqld called /usr/sbin/mysqld2 using the command:
ln -s /usr/sbin/mysqld /usr/sbin/mysqld2
Create the new Database Directory and make a copy of the existing databases to /var/lib/mysql2.
Note: make sure the mysqld daemon is not running during this.
mkdir /var/lib/mysql2
cp -a /var/lib/mysql /var/lib/mysql2

Give ownership of the databases to mysql user using the command:
chown -R mysql. /var/lib/mysql2
Enter the new init script in default runlevels with the command:
update-rc.d mysql2 defaults
[Optional] Create a symlink for manual start/stop with the command:
ln -s /etc/init.d/mysql2 /usr/sbin/rcmysql2
Start the both mysql databases with the commands:
rcmysql start
rcmysql2 start

You will have a replica of the original mysql database running on port 3307 as well.

16 Mar 10 Various MySQL useful commands

Repairing all MySQL tables
mysqlcheck --repair --extended --all-databases
Dumping the content of all databases in SQL format. (can be used for backup/migration purposes)
mysqldump -p --user=root --add-drop-table --all-databases > all_my_databases.sql

Import all databases from above saved file
mysql -u root -p < all_my_databases.sql
Dumping the content of a single database in SQL format.(can be used for backup/migration purposes)
mysqldump -p --user=root --add-drop-table databasename > my_database.sql

Import a database from above saved files
mysql -u root -p databasename < my_database.sql
Checking the integrity of mysql databases
mysqlcheck -A -p

24 Feb 10 Retrieving lost MySQL root password

It often happens. You are given the job of administrating a MySQL server where the root password was lost.
What to do? Here is the solution I found in Internet at the link below. Thanks for the useful tip.

by Vivek Gite · 121 comments

You can recover MySQL database server password with following five easy steps.
Step # 1: Stop the MySQL server process.
Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.
Step # 3: Connect to mysql server as the root user.
Step # 4: Setup new mysql root account password.
Step # 5: Exit and restart the MySQL server.

Here are commands you need to type for each step (login as the root user):
Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started
Well for some unknwon reason mysql server didn’t start (in Debian Lenny)
So to solve this I modified the start/stop script (/etc/init.d/mysql) as follows:
Around line: 100 (Addition is in bold here)
This way it retained its environment etc. to be able to start…and it worked 🙂
# Start daemon
log_daemon_msg "Starting MySQL database server" "mysqld"
if mysqld_status check_alive nowarn; then
log_progress_msg "already running"
log_end_msg 0
/usr/bin/mysqld_safe --skip-grant-tables > /dev/null 2>&1 &
# 6s was reported in #352070 to be too few when using ndbcluster
for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do


Step # 3: Connect to mysql server using mysql client:

# mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.


Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/
mysqld_safe[6186]: ended

[1]+ Done mysqld_safe –skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

03 Jan 10 Tool to unstick MySQL Replicators.

# I’m managing an infrastructure with a number of databases who
# (for codified reasons that I cannot influence) suffer from this situation often.
# So, I’ve written a cron script to manage the situation.
# Does anyone see any foreseeable issues with this logic (see below)?

# —————————————– SCRIPT —————————————–
## Tool to unstick MySQL Replicators.
## Set to run from cron once a minute.

# */1 * * * * /usr/local/bin/ > /dev/null 2>&1

# Last updated: MM/DD/YYYY

COMMANDS=”mysql grep awk logger”
export PATH=’/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin’
for i in $COMMANDS
X=`echo $i | tr ‘[a-z]’ ‘[A-Z]’`
export $X=`type -p $i`

# Define variables

# Define Functions
## Obtain MwSQL slave server status
function SLAVE()
$GREP Seconds_Behind_Master |
$AWK ‘{print $2}’`

## Skip errors
function UNSTICK()
sleep 5
# Check everything again

## Decide what to do…
function CHECK()
# Obtain status
if [ $STATUS = NULL ]
# I think the replicator is broken
echo “MySQL Slave database is not replicating. Fixing…” | $LOGGER
# Everything should be fine
echo “MySQL Slave is $STATUS seconds behind its Master.” | $LOGGER

## Are we running?
function ALIVE()
$GREP Slave_IO_Running |
$AWK ‘{print $2}’`

if [ $UP = Yes ]
# Let’s check if everything is good, then…
# Uh oh…let’s not do anything.
echo “MySQL Slave IO is not running!” | $LOGGER
exit 1

# How is everything?

exit 0

03 Jan 10 Slave rebuild from live slave.

It is also possible to set up a slave by dumping an existing slave of the master.
To do this, use the following procedure on the existing slave:

1.Stop the slave’s SQL thread and get its current status:


2.From the output of the SHOW SLAVE STATUS statement, get the binary log coordinates of the master server
from which the new slave should start replicating. These coordinates are the values of the
Relay_Master_Log_File and Exec_Master_Log_Pos values. Denote those values as file_name and file_pos.

3.Dump the slave server:
shell> mysqldump –master-data=2 –all-databases > dumpfile

4.Restart the slave:

5.On the new slave, reload the dump file:
shell> mysql < dumpfile 6.On the new slave, set the replication coordinates to those of the master server obtained earlier: mysql> CHANGE MASTER TO
-> MASTER_LOG_FILE = ‘file_name’, MASTER_LOG_POS = file_pos;

The CHANGE MASTER TO statement might also need other parameters, such as MASTER_HOST to point the slave to the correct master server host. Add any such parameters as necessary.


“Flushing the logs”, in MySQL parlance, means closing the current log and opening a new one.
It does not get rid of old logs.
Old logs can be removed explicitly with RESET MASTER or PURGE MASTER LOGS,
else they expire automatically according to the expire_logs_days system variable.


I tried the same thing but after following the step suggested:

CHANGE MASTER TO master_log_file=’name_of_current_file_on_master’,master_log_pos=4;

I repeated the step with old log name and old file position before starting the slave, didn’t loose any data and the stuff worked perfect.
Not sure if it is a bug as it happened to two of the servers in both cases after reboot.

Another solution:
reset slave;
start slave;

you can also do,
reset slave —-This will remove/delete any old master and relay log
and use the CHANGE MASTER command.


03 Jan 10 PROBLEM: Could not find target log during relay log initialization

delete relay bin file in slave and try
slave stop ;
CHANGE MASTER TO master_log_file='name_of_current_file_on_master',master_log_pos=4;
slave start;

it will download play again the file
IMPORTANT: Make sure the master log file is the one that was being read by the slave when the error occured,
otherwise you loose data.

03 Jan 10 FULL Manual rebuilding of SLAVE MySQL server mysql02 from Master mysql01

On mysql01 (MASTER):
mysqldump -u root -p –single-transaction –flush-logs –master-data=2 –all-databases –delete-master-logs
| gzip | ssh mysql02 ‘cat | gunzip >/var/lib/mysql_temp.sql’

echo “SHOW MASTER STATUS;” | mysql -p -u root

Note the filename eg. mysql-bin.000299
we will use it later on mysql02

On mysql02 (SLAVE):
– Changed the following config is in /etc/mysql/my.cnf

126 = the ambiguous ‘unknown error on master’ typically caused by tables in dis-repair on master.
1062 = duplicate entry (happens all the time on high capacity db in master-master setup)

– Using phpmyadmin, delete all databases except ‘mysql’ and ‘Information schema’

– echo “STOP SLAVE; RESET SLAVE;” | mysql -p -u root

– mysql -p -u root < /var/lib/mysql_temp.sql <-----SQL file produced by mysqldump in mysql01 - mysql -p -u root > CHANGE MASTER TO master_log_file=’mysql-bin.000299′,master_log_pos=4;
Note: ‘mysql-bin.000299’ is the current bin log filename on master above


Value of Read_Master_Log_Pos: should = Exec_Master_Log_Pos: value.
Repeat the command a few times again and each time check if they follow each other.

03 Jan 10 MySQL replication issues

Good links:

MySQL recover from Backup .sql data
mysql -u root -p dbName backup-dbNameDump.sql

———– Very useful in slave ————————————————————–
126 = the ambiguous ‘unknown error on master’ typically caused by tables in dis-repair on master.
1062 = duplicate entry (happens all the time on high capacity db in master-master setup)