Please create this script
Fron the Linux prompt, open up an editor like vi
vi /root/CreateMySQLSlave.sh
and add these lines to it
MYSQL_MASTER_HOST=10.1.2.20
MYSQL_SLAVE_HOST=10.1.2.30
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_MASTER_CONN="-h${MYSQL_MASTER_HOST} ${MYSQL_CONN}"
MYSQL_SLAVE_CONN="-h${MYSQL_SLAVE_HOST} ${MYSQL_CONN}"
MYSQLDUMP_OPTIONS="--master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
CREATE_REPL_USER="GRANT REPLICATION SLAVE ON *.* TO repluser@'%' IDENTIFIED BY 'replpass'"
mysql ${MYSQL_MASTER_CONN} -AN -e"${CREATE_REPL_USER}"
RELOAD_FILE=/root/MySQLData.sql
echo "STOP SLAVE;" > ${RELOAD_FILE}
echo "CHANGE MASTER TO master_host='${MYSQL_MASTER_HOST}'," >> ${RELOAD_FILE}
echo "master_port=3306," >> ${RELOAD_FILE}
echo "master_user='repluser'," >> ${RELOAD_FILE}
echo "master_password='replpass'," >> ${RELOAD_FILE}
echo "master_log_file='dummy-file'," >> ${RELOAD_FILE}
echo "master_log_pos=1;" >> ${RELOAD_FILE}
mysqldump ${MYSQL_MASTER_CONN} ${MYSQLDUMP_OPTIONS} >> ${RELOAD_FILE}
echo "START SLAVE;" >> ${RELOAD_FILE}
mysql ${MYSQL_SLAVE_CONN} -A < ${RELOAD_FILE}
Next, make the file executable and run it
chmod +x /root/CreateMySQLSlave.sh
/root/CreateMySQLSlave.sh
That's it !!!
Make sure you set the IP Address for MYSQL_MASTER_HOST
and MYSQL_SLAVE_HOST
By the way, don't worry about the dummy-file
in the master_log_file parameter for CHANGE MASTER TO
. Line 29 of the dump file should have the correct setting there due to the --master-data=1
. You can see it by doing this to the dump file
head -29 /root/MySQLData.sql | tail -1
Make sure binary logging is already enabled on the master. Here is how you can tell: run SHOW MASTER STATUS;
. If you get nothing back, you need to enable it like this:
Add this to /etc/my.cnf
[mysqld]
log-bin=mysql-bin
then restart mysql
OK that takes care of binary logging for the Master.
You also need to make sure the Master and the Slave have different server IDs. Here is how you can tell:
Run SHOW VARIABLES LIKE 'server_id';
If the result comes up as 1
, you must set it explicitly.
Goto the Master and add this to /etc/my.cnf
[mysqld]
server-id=1
Goto the Slave and add this to /etc/my.cnf
[mysqld]
server-id=2
The main thing here is to make sure server_id
is explicitly set in my.cnf on both Master and Slave. You must also make sure the server_id values are different from all other servers involved in the replication topology.
If you are new to this, have a sysadmin join you to do this.
It might be in the manual:
Each MySQL Server can set its own and only its own binary logging
format (true whether binlog_format is set with global or session
scope). This means that changing the logging format on a replication
master does not cause a slave to change its logging format to match.
(When using STATEMENT mode, the binlog_format system variable is not
replicated; when using MIXED or ROW logging mode, it is replicated but
is ignored by the slave.)
-- http://dev.mysql.com/doc/refman/5.6/en/binary-log-setting.html
That is "Yes" to your questions.
Note that ROW
is the default for 5.7. That is, ROW
has proven itself to be the 'right' way to do replication. Galera clustering can't even work without ROW
.
Best Answer
Replication is always serialized to prevent the race-conditions you are speaking about. See this stackoverflow post for my explanation of InnoDB locking. Some important notes:
RBR with row-based replication and read-committed isolation level sets fewer locks than InnoDB would have previously.
We should be moving to a world with RBR anyway, because this will open more doors for parallel replication threads (by working out dependencies between transactions).
To answer your last question, I use RBR in production, and I haven't seen any major performance hit. We do generate a lot more binary log data however, since the events logged contain whole rows.