We will need two Ubuntu Servers.
for example:
hostname: IP
--------------------------------------
ubuntu-one: 10.66.66.36ubuntu-two: 10.66.66.29To begin with we'll install and configure MySQL for normal use on each of the boxes.
sudo apt-get install mysql-server --yes
Set a strong MySQL root password and wait for the packages to download and install, then edit /etc/mysql/my.cnf to make MySQL listen on all IP addresses.
bind-address = 0.0.0.0
Now restart MySQL and fire up the MySQL command-line client to check all is good.
sudo /etc/init.d/mysql restart
mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql>
If you got the mysql> prompt then MySQL is running. Try connecting to the other node across the network to see if the firewall is opened and MySQL is listening on the network interface.
From ubuntu-one run:
mysql -h ubuntu-two -u root -p
Enter password: [enter the MySQL root password you chose earlier]
ERROR 1130 (00000): Host 'db-01' is not allowed to connect to this MySQL server
If you got the above error then everything is working fine - MySQL connected and refused to authorise the client.
The first thing we want to do is setup a simple master-slave
replication to see that it's possible to replicate data from one
database host to the other. This requires a binary log so tell MySQL on
ubuntu-one to keep one.
Edit
/etc/mysql/my.cnf and set the following values under the replication section:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = my_application <=== Database to replicate
binlog_ignore_db = mysql <=== Database to ignore
binlog_ignore_db = test <=== Database to ignore On ubuntu-one grant\
replication slave rights to ubuntu-two.
Change some_password to a real, strong password.
Afterwards, make sure you restart MySQL.
mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql> grant replication slave on *.* to 'replication'@'ubuntu-two' identified by 'some_password';
sudo /etc/init.d/mysql restart
Jump on to ubuntu-two and set it up to replicate data from ubuntu-one by editing /etc/mysql/my.cnf, again replacing the hostname, username and password with the values for ubuntu-one.
server-id = 2
master-host = ubuntu-one
master-user = replication
master-password = some_password
master-port = 3306One way replication should now be setup. Restart MySQL and check the
status of the slave on ubuntu-two. If the Slave_IO_State is "Waiting for
master to send event" then you've been successful.
# Run this on ubuntu-two only
sudo /etc/init.d/mysql restart
mysql -u root -p
Enter password: [enter the MySQL root password you chose earlier]
mysql> show slave status ;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 193.219.108.241
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000004
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
