0 like 0 dislike
42 views
in Education & Reference by (3k points)

1 Answer

0 like 0 dislike
by (850 points)
selected by
 
Best answer

Configuration for Master 1 – 10.1.1.1
In your my.cnf file (on Debian 5 it’s located at /etc/mysql/my.cnf, on CentOS/RHEL 5 it’s at /etc/my.cnf) add the following:

server-id=1
log-bin=mysql-bin
port=3306
max_allowed_packet=256M
binlog_do_db= DatabaseName
expire_logs_days = 20
max_binlog_size = 500M
auto_increment_increment = 2
auto_increment_offset = 1
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

And run the following SQL command as the root user:

grant all on *.* to ‘msandbox’@’22.22%’ identified by ‘msandbox’;
flush privileges;

Restart the MySQL server.

mysql -u root -p
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The output should look like this. Note down the file and the position – you’ll need both later.

+——————+———-+———————+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+———————+——————+
| mysql-bin.000004 | 98 | exampledb,exampledb | |
+——————+———-+———————+——————+
1 row in set (0.00 sec)

Open a second terminal for system 1, create the dump and transfer it to system 2.
Don’t leave the MySQL-shell at this point – otherwise you’ll loose the read-lock (If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.)

cd /tmp/
mysqldump -u root -p%mysql_root_password% –opt exampledb > sqldump.sql
scp sqldump.sql [email protected]:/tmp/

Afterwards close the second terminal and switch back to the first. Remove the read-lock and leave the MySQL-shell.

UNLOCK TABLES;
quit;

Configuration Slave – 10.1.1.2

CREATE DATABASE newdatabase;
EXIT;

Time to import the database dump on system 2.

mysql -h22.22.22.23 -umsandbox -P5173 -p DBNAME < /var/backup/DB_Backup.sql

In your my.cnf file (on Debian 5 it’s located at /etc/mysql/my.cnf add the following:

server-id = 2
log-bin=mysql-bin
binlog-do-db= DatabaseName
lower_case_table_names =1

Restart the MySQL server.

Enter into MYSQL(Slave)

–STOP SLAVE
– CHANGE MASTER TO MASTER_HOST=’12.34.56.789′,MASTER_USER=’slave_user’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS= 107;
START SLAVE;
SHOW SLAVE STATUS\\G

… and take a look at the slave status. It’s very important that both, Slave_IO_Running and Slave_SQL_Running are set to Yes. If they’re not, something went wrong and you should take a look at the logs.

Related questions

1 like 0 dislike
1 answer 186 views
0 like 0 dislike
1 answer 154 views
0 like 0 dislike
1 answer 165 views
0 like 0 dislike
1 answer 67 views
1 like 0 dislike
1 answer 242 views
0 like 0 dislike
1 answer 103 views

Where your donation goes
Technology: We will utilize your donation for development, server maintenance and bandwidth management, etc for our site.

Employee and Projects: We have only 15 employees. They are involved in a wide sort of project works. Your valuable donation will definitely boost their work efficiency.

How can I earn points?
Awarded a Best Answer 10 points
Answer questions 10 points
Asking Question -20 points

...