MySQL Forums
Forum List  »  Replication

Re: Replication setup (for the first time)
Posted by: Rick James
Date: May 25, 2014 04:45PM

> mysql> FLUSH TABLES WITH READ LOCK;

Or, simply stop all writers.

> mysql> SHOW MASTER STATUS;

Not really needed -- restarting mysqld will cause a new binlog to be started; POS=0 (or 4) in the new binlog is what to use for the CHANGE MASTER.

> I am not sure if I better use z parameter for compression or not?

If the link between the machines is very high speed, then compression would probably cost more CPU time than it would save in network time. (For WANs, compression is often a good idea.)

> 7. Copy Database files from Master to Slave

The entire tree, I assume. But not my.cnf.

> 8. Rename the copied "mysql" system database in the SLAVE as "mysql_master" and then copy the original mysql. -- I am not sure if I better preserving the old one too? Do you think so? With this configuration, is mysql db replicated?

The Master's `mysql` database is not needed on the Slave. It is better to use the installed `mysql` on the Slave, then GRANT any users that are needed. ("repl_user" is not needed on the Slave.)

> master-host=MASTER.domain.com
> master-user=repl_user
> master-password=xxx

Yes, you can do that in 5.1. But it is going away. It is better to set these via CHANGE MASTER. Also, it is more "secure".

> First, please let me tell you why I did not prefer mysqldump but copying database files, because I do not have enough disk space for an extra operation.

That is not a problem: On any machine do
mysqldump -h MASTER... | mysql -h SLAVE...

Another way to do the setup is to use Percona's XtraBackup -- it handles many of the details that you are doing manually. And, if 'all' your tables are InnoDB, you can shrink the "maintenance window" from 10 hours down to more like 10 minutes.

> The database files including the Tablespace are 500GB in size.

Do you mean ibdata1? If so, and if you use mysqldump or XtraBackup, then I recommend this on the new machine:
innodb_file_per_table = 1

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Replication setup (for the first time)
980
May 25, 2014 04:45PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.