Heads Explode Here
MySQL slave on CentOS
Got a CentOS box running as a slave of my Ubuntu server.
Here are the steps I took, starting on the slave:
# yum install mysql-server*Then we ought to secure our installation:
# /usr/bin/mysql_secure_installationOn the master, we need to create a user for the slave to connect as (adjust user name & IP address to suit your needs):
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.1.%' IDENTIFIED BY PASSWORD [your-choice-of-password]Now, as extra precaution, lock all tables for reading only (our mysqldump will --lock-all-tables too):
mysql> FLUSH TABLES WITH READ LOCK;That will lock out writing to the tables (all tables, all databases) until while we get a dump of the data. Do not close this mysql session else the locks will be released. When running mysqldump with the "--master-data", it will set "--lock-all-tables" there too, so this might be a bit redundant.
I like to dump the databases individually, so I use a script for that, but here is how to simply dump all of them (run in a Bash shell):
$ mysqldump --create-options --master-data=1 --hex-blob --tz-utc --add-drop-database --disable-keys --flush-logs --add-locks --no-autocommit --all-databases > load-on-slave.sqlNow we can release the lock on the master:
mysql> unlock tables ;On the slave, we need to get our dumped data:
ftp [master host]
ftp> bin
ftp> prompt
ftp> mget load-on-slave.sql
ftp> quitWe also need the slave's /etc/my.cnf configuration file set to slave mode:
/etc/my.cnf:
master-user = [slave's user name]
master-password = [slave's password]
master-port = 3306
Nearing completion, start mysqld on slave, then issue a CHANGE-MASTER-TO statement setting the master_host (adjust to your needs), master_user (the user created on master, above), and master_password:
mysql> CHANGE MASTER TO master_host='192.168.1.##', master_user='slave', master_password='[password]';Finally, load the data into the slave and start the slave, password option omitted in this example; you might need it:
In Bash:
$ mysql --user=[you] Now, initiate the slave process:
myql> start slave;See if things appear to be working:
mysql> show slave status \GYou can now create a database on the master "CREATE DATABASE ZZZ;", then "SHOW DATABASES;" on the slave to see if it appears in the list. A "DROP DATABASE ZZZ;" should make the ZZZ database disappear from the master and the slave.
Further details on master-slave replication on version 5.1 is available on MySQL's web site (or, for version 5.5 here.)
Comments
Ron
Thu, 2014/02/20 - 03:24
Permalink
Error recovery
It's occassionally possible to have the slave report an error condition with
show slave status\G.If the error is one that can be skipped over (i.e. during non-critical testing), it's possible to skip the error with a simple step:
set global sql_slave_skip_counter=1;
start slave;
For example, the disturbing issue of MySQL's UTF8 support (it's pitiful, use utf8mb4, see Mathias Bynens' post on "broken" UTF8 vs UTF8MB4 support (version 5.5.3 introduces UTF8MB4)).
On the master, running
Server version: 5.5.28-log Source distribution, we'll make a table with default character set of UTF8MB4:Query OK, 0 rows affected (0.10 sec)
Now, checking the slave, running
Server version: 5.1.71 Source distribution, we see an error as UTF8MB4 is not supported (it is not recommended to run different versions of MySQL between master and slave, but I don't think I have resources to compile MySQL on the slave machine, and it gives opportunities for compatibilty testing):...
Last_Errno: 1115
Last_Error: Error 'Unknown character set: 'utf8mb4'' on query. Default database: 'test'. Query: 'create table utf8error (name varchar(20) character set utf8mb4)'
Now, to clear that error:
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
And presto - error gone:
...
Last_Errno: 0
Last_Error:
Of course, in this case one needs to deal with the underlying issue as the master has a table called
utf8errorand the slave doesn't -- anddrop table utf8erroron the server will generate yet another error on the slave.R o n
- - -
Vancouver
Add new comment