Mysql replication on same machine

Posted by on March 9, 2019

1. Install mysql and configure servers

sudo apt-get install mysql-server
sudo nano /etc/mysql/my.cnf

Add or edit these lines:

[mysql1]
server-id           = 1

[mysql2]
server-id           = 2
port                = 3307
socket              = /var/run/mysqld/mysqld_slave.sock
pid-file            = /var/run/mysqld/mysqld_slave.pid
datadir             = /var/lib/mysql_slave
log_error           = /var/log/mysql_slave/error_slave.log
relay-log           = /var/log/mysql_slave/relay-bin
relay-log-index     = /var/log/mysql_slave/relay-bin.index
master-info-file    = /var/log/mysql_slave/master.info
relay-log-info-file = /var/log/mysql_slave/relay-log.info
read_only           = 1

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = multipass

Create folders for slave server:

mkdir -p /var/lib/mysql_slave
chmod --reference /var/lib/mysql /var/lib/mysql_slave
chown --reference /var/lib/mysql /var/lib/mysql_slave
mkdir -p /var/log/mysql_slave
chmod --reference /var/log/mysql /var/log/mysql_slave
chown --reference /var/log/mysql /var/log/mysql_slave

AppArmor settings:

sudo nano /etc/apparmor.d/usr.sbin.mysqld
/var/lib/mysql_slave/ r,
/var/lib/mysql_slave/** rwk,
/var/log/mysql_slave/ r,
/var/log/mysql_slave/* rw,
/var/run/mysqld/mysqld_slave.pid rw,
/var/run/mysqld/mysqld_slave.sock w,
/run/mysqld/mysqld_slave.pid rw,
/run/mysqld/mysqld_slave.sock w,

Reboot

 

2. Create mysqld_multi

initialize new MySQL data directory:

mysql_install_db --user=mysql --datadir=/var/lib/mysql_slave


Setup a password for slave

mysqladmin --host=127.0.0.1 --port=3307 -u root password rootpwd

Grant privileges to multi_admin

sudo mysql
mysql> GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY "multipass";
mysql> FLUSH PRIVILEGES;

mysql --host=127.0.0.1 --port=3307 -uroot -p
mysql> GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY "multipass";
mysql> FLUSH PRIVILEGES;

Create service to start both instances at boot

cd /etc/init.d
sudo touch mysqld_multi
sudo chmod +x /etc/init.d/mysqld_multi
sudo nano init.d
#!/bin/sh

### BEGIN INIT INFO
# Provides:       scriptname
# Required-Start: $remote_fs $syslog
# Required-Stop:  $remote_fs $syslog
# Default-Start:  2 3 4 5
# Default-Stop:   0 1 6
# Short-Description: Start daemon at boot time
# Description:    Enable service provided by daemon.
### END INIT INFO

bindir=/usr/bin

if test -x $bindir/mysqld_multi
then
   mysqld_multi="$bindir/mysqld_multi";
else
   echo "Cannot execute $bindir/mysqld_multi";
   exit;
fi

case "$1" in
   "start" )
    "$mysqld_multi" start $2
    ;;
   "stop" )
    "$mysqld_multi" stop $2
    ;;
   "report" )
    "$mysqld_multi" report $2
    ;;
   "restart" )
    "$mysqld_multi" stop $2
    "$mysqld_multi" start $2
    ;;
   *)
    echo "Usage: $0 {start|stop|report|restart}" >&2
    ;;
esac

update-rc.d mysqld_multi defaults

Reboot

 

3. Replication

sudo nano /etc/mysql/my.cnf

Add on mysqld1

log_bin                     = /var/log/mysql/mysql-bin.log
innodb_flush_log_at_trx_commit  = 1
sync_binlog                 = 1
binlog-format               = ROW

 

mysqld_multi stop 1
mysqld_multi start 1

sudo mysql
mysql> CREATE USER replication@% IDENTIFIED BY "replication";
mysql> GRANT REPLICATION SLAVE ON *.* TO replication@%;

 

sudo mysqldump --all-databases --master-data=2 > replicationdump.sql
head replicationdump.sql

Note: MASTER_LOG_FILE MASTER_LOG_POS to copy later on 2

mysql -uroot -p --host=127.0.0.1 --port=3307 < replicationdump.sql
mysql -uroot -p --host=127.0.0.1 --port=3307

mysql> CHANGE MASTER TO
 -> MASTER_HOST="127.0.0.1",
 -> MASTER_USER="replication",
 -> MASTER_PASSWORD="replication",
 -> MASTER_LOG_FILE="mysql-bin.00000x",
 -> MASTER_LOG_POS=xxx;

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS G