Is there a way to replicate master/slave without disturbing master for dump like mysqldump or cold copy of data files? I'm ok to bring down the instance only once for master to enable log-bin. But there is no feasibility for dump or even cold copy. As I have 400G of data and 1min down time only.
Mysql – replicate master/slave without disturbing master for dump
MySQL
Related Solutions
Going by your question, I will like to review what I believe you did thus far:
- You stopped mysql on the Master
- You copied Master's /var/lib/mysql to the Slave's /var/lib/mysql
- I surmise the binlogs on the Master were copied as well
Look at the Slave's last binlog. From the question, it should be
mydbm1-bin.008524
- Filesize 1330529
Believe it or not, you have to do a few things:
1) On the Master, create a replication user like this:
GRANT REPLICATION SLAVE,REPLICATION CLIENT
ON *.* TO replicator@'%'
IDENTIFIED BY 'r3plic4t0R';
2) Make /var/lib/mysql on the Slave owned by mysql
user
chown -R mysql:mysql /var/lib/mysql
3) Make sure Master's server_id is explicitly set in my.cnf
[mysqld]
server_id = 1
4) Make sure Slave's server_id is explicitly set in my.cnf
[mysqld]
server_id = 2
5) Startup mysql on the Slave
service mysql start
6) Setup replication by running this on the Slave
CHANGE MASTER TO
MASTER_HOST='IPAddressOfMaster',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mydbm1-bin.008524',
MASTER_LOG_POS=1330529;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G
You will see something like this:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.48.20.253
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000254
Read_Master_Log_Pos: 858190247
Relay_Log_File: relay-bin.066069
Relay_Log_Pos: 873918
Relay_Master_Log_File: mysql-bin.000254
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: 858190247
Relay_Log_Space: 873772
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
1 row in set (0.00 sec)
If Slave_IO_Running
and Slave_SQL_Running
are both Yes
, CONGRATULATIONS !!!
I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with essentially the same steps.
I wanted to add additional posts I made for setting up Circular Replication should you decide to setup the two DB servers as Master/Master
Sep 24, 2012
: Setting up MySQL circular replication in existing replication topology?May 07, 2012
: Setting Circular Replication in mysql
@RolandoMySQLDBA's answer is accurate, but I would advise against using the replicate-*-table
and replicate-*-db
options because of the complexity of how MySQL evaluates replication rules.
When these aren't used, everything is replicated, and I would contend that replicating everything the most reliable configuration. As a rule, I never do anything else.
If you don't want to replicate the historical data, I would start by setting up a full, standard master/slave replication setup...
...then...
...identify the tables you don't care about on the replica and for each such table, on the slave:
ALTER TABLE table_name ENGINE=BLACKHOLE;
This will discard all of the data in those tables on the slave and convert them to the blackhole storage engine, which accepts inserts (but doesn't store them), returns empty result-sets from selects, and has "0 rows affected" on updates and deletes... so your schemata are still fully compatible but you're not actually storing anything in the tables you're not interested in.
If you're using mysqldump
to initially set up the replica, you can also manually edit the dump files and change the table engine on those tables' declarations.
All of the data will still be "replicated to" the slave, but not "saved on" the slave... the data written to these tables will not be stored. Writing rows to a blackhole table is a very resource-friendly operation -- I've seen my servers handle 40,000+ queries per second when replicating "into" blackhole tables.
If you're concerned about the fact that the data is still transferred to the replica... or, really, even if you're not, just turn on slave_compressed_protocol
on the slave and don't think about it -- consider it the cost of a cleaner replication setup. When the slave connects to the master, it will negotiate a connection using the MySQL compressed client protocol, substantially reducing the actual bytes of data transferred on the wire between the machines, but still sending everything.
Related Question
- MySQL Master Slave Replication and backup
- MySQL Replication Master push to Slave
- Mysql – replicate slaves without a dump
- Mysql – Automatic slave reconfiguration after updating master
- Mysql – Starting a slave from behind master
- Mysql – Can a MySQL 5.6 master replicate to a 5.7 slave, which in turn replicates to a MySQL 8 slave
- MySQL master slave replication-troubleshooting
Best Answer
I would recommend Percona's Xtrabackup for this, especially since you mentioned all of your tables are InnoDB. From the page:
The tool will allow you to take a backup without locking innodb tables. It will do a lock to copy any MyISAM tables (such as the tables
mysql
in the mysql database) but those shouldn't take long.I cannot tell you how long it will take on 400GB database, but it should do it without downtime.