I really wanted to be able to import text files into a column for a little project I'm working on. I know that MySQL isn't the ideal place for storing TEXT files but the project was small and so this is what I wanted. But the LOAD_FILE simply wouldn't work. Eventually I found that someone had posted a work around. You have to put the files in directoy inside of this directory: /var/lib/mysql and then give the correct permissions.
I found the information from here for the "ugly workaround":
http://stackoverflow.com/questions/4607486/mysql-load-file-returning-null
Steps I performed:
Create a new directory under mkdir -p /var/lib/mysql/:
mkdir -p /var/lib/mysql/upload_data/
Create a test file:
ps -ef > /var/lib/mysql/upload_data/test_import.txt
Verify the file has some data:
head /var/lib/mysql/upload_data/test_import.txt
Changing permissions wasn't needed on my system but others said it might be needed:
chown mysql:mysql /var/lib/mysql/upload_data/*
chmod go+rw /var/lib/mysql/upload_data/*
Give your user the needed permissions:
mysql -u root
mysql>
GRANT file ON *.* to 'test'@'%';
GRANT file ON *.* to 'test'@'localhost';
GRANT ALL PRIVILEGES ON test.* TO 'test'@'%' IDENTIFIED BY 'test_password';
GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost' IDENTIFIED BY 'test_password';
exit
mysql -u test
mysql> SHOW GRANTS;
DROP TABLE IF EXISTS `test`.`table_a`;
CREATE TABLE IF NOT EXISTS `test`.`table_a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`output` text COLLATE utf8_unicode_ci COMMENT 'program output from command execution if available',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='stores test files';
REPLACE INTO `test`.`table_a` (`id`, `output`) VALUES (1, NULL);
SELECT hex(LOAD_FILE('/var/lib/mysql/upload_data/test_import.txt'));
UPDATE test.table_a
SET output=LOAD_FILE('/var/lib/mysql/upload_data/test_import.txt')
WHERE id=1;
SELECT * FROM test.table_a WHERE id =1;
One of the problems with this ugly work around is the new folder in var/lib/mysql/ will look like a new database (unless your data directory is some where else). One way to get around this it to make the folder hidden by starting the folder name with a period.
Tuesday, September 27, 2016
Monday, September 19, 2016
Replication errors on tables in mysql schema ERROR 1146 (42S02): Table doesn't exist
I've been trying to setup orchestrator but when making a slave a co-master I would get errors like this:
2016-08-29 16:42:52 ERROR ReadTopologyInstance(server_name:3306) show slave hosts: Will not resolve empty hostname
In order to get passed that error, I needed to create the slave_master_info table on each server. And then after creating the table, I would grant SELECT access to the orchestrator user.
This is what I was running on my servers (in addition to modifying the my.cnf):
STOP SLAVE;
SET GLOBAL master_info_repository = "TABLE";
START SLAVE;
COMMIT;
However, on some servers (maybe 10% of them), my attempts to create it on a slave would break replication and then the table wouldn't exist when I would grant SELECT access. I would end up with an error like this:
Error 'Table 'mysql.slave_master_info' doesn't exist' on query. Default database: ''. Query: 'GRANT SELECT ON mysql.slave_master_info TO ...
I found this useful blog post:
That blog post mentioned yet another post:
2016-08-29 16:42:52 ERROR ReadTopologyInstance(server_name:3306) show slave hosts: Will not resolve empty hostname
In order to get passed that error, I needed to create the slave_master_info table on each server. And then after creating the table, I would grant SELECT access to the orchestrator user.
This is what I was running on my servers (in addition to modifying the my.cnf):
STOP SLAVE;
SET GLOBAL master_info_repository = "TABLE";
START SLAVE;
COMMIT;
GRANT SELECT ON mysql.slave_master_info TO 'xxx'@'%' IDENTIFIED BY PASSWORD '*xxx';
However, on some servers (maybe 10% of them), my attempts to create it on a slave would break replication and then the table wouldn't exist when I would grant SELECT access. I would end up with an error like this:
Error 'Table 'mysql.slave_master_info' doesn't exist' on query. Default database: ''. Query: 'GRANT SELECT ON mysql.slave_master_info TO ...
I found this useful blog post:
http://anothermysqldba.blogspot.com/2013/09/error-1146-42s02-table-doesnt-exist.html
That blog post mentioned yet another post:
http://bazaar.launchpad.net/~mysql/mysql-server/5.6/view/head:/scripts/mysql_system_tables.sql#L103
By creating the slave_master_info table like this on the servers where replication was broken, I was able to get passed these errors:
use mysql;
CREATE TABLE IF NOT EXISTS `slave_master_info` (
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
`Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
`User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
`User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
`Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
`Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
`Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
`Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
`Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
`Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
`Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
`Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
`Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
`Heartbeat` float NOT NULL,
`Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
`Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
`Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
`Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
`Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
`Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
STOP SLAVE;
SET GLOBAL master_info_repository = "TABLE";
START SLAVE;
GRANT SELECT ON mysql.slave_master_info TO 'xxx'@'%' IDENTIFIED BY PASSWORD '*xxx';
Tuesday, September 13, 2016
Setting up orchestrator
I've been testing Orchestrator for MySQL and after following the installation instructions, I kept getting these error and simply could not figure out what was wrong. I opened up a ticket and within a day, the developer got back to me and let me know what was probably wrong. I had setup replication and the MySQL instances with IP address on my local network and never setup DNS entries. Orchestrator used the hostname, not the IP address to setup replication. Every time I issued an orchestrator command, replication would start to fail. Here is the issue I opened on GitHub:
https://github.com/outbrain/orchestrator/issues/249
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex2:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex1:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex2:3306) Cleanup: dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex1:3306) Cleanup: dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex2:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex1:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex0:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex0:3306) Cleanup: dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex0:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:28 DEBUG outdated keys: []
2016-08-29 01:38:29 DEBUG outdated keys: []
2016-08-29 01:38:30 DEBUG outdated keys: []
2016-08-29 01:38:31 DEBUG outdated keys: []
2016-08-29 01:38:32 DEBUG outdated keys: []
2016-08-29 01:38:33 DEBUG outdated keys: [delloptiplex0:3306 delloptiplex1:3306 delloptiplex2:3306]
Next I noticed more errors with Orchestrator:
2016-08-29 16:42:52 ERROR ReadTopologyInstance(delloptiplex1:3306) show slave hosts: Will not resolve empty hostname
I got passed those by adding this into the my.cnf file for each mysql instance:
report_host=my_hostname.example
Here is a good post that illustrates that:
https://avdeo.com/2015/04/19/show-slave-hosts-on-master-not-reporting-hostname/
https://github.com/outbrain/orchestrator/issues/249
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex2:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex1:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex2:3306) Cleanup: dial tcp: lookup delloptiplex2 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex1:3306) Cleanup: dial tcp: lookup delloptiplex1 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex2:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex1:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:27 ERROR dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex0:3306) show variables like 'maxscale%': dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 ERROR ReadTopologyInstance(delloptiplex0:3306) Cleanup: dial tcp: lookup delloptiplex0 on 8.8.8.8:53: no such host
2016-08-29 01:38:27 WARNING instance is nil in discoverInstance. key=delloptiplex0:3306, error=Failed ReadTopologyInstance
2016-08-29 01:38:28 DEBUG outdated keys: []
2016-08-29 01:38:29 DEBUG outdated keys: []
2016-08-29 01:38:30 DEBUG outdated keys: []
2016-08-29 01:38:31 DEBUG outdated keys: []
2016-08-29 01:38:32 DEBUG outdated keys: []
2016-08-29 01:38:33 DEBUG outdated keys: [delloptiplex0:3306 delloptiplex1:3306 delloptiplex2:3306]
Next I noticed more errors with Orchestrator:
2016-08-29 16:42:52 ERROR ReadTopologyInstance(delloptiplex1:3306) show slave hosts: Will not resolve empty hostname
I got passed those by adding this into the my.cnf file for each mysql instance:
report_host=my_hostname.example
Here is a good post that illustrates that:
https://avdeo.com/2015/04/19/show-slave-hosts-on-master-not-reporting-hostname/
Monday, September 5, 2016
Clearing out the results of show slave status hosts after removing a slave
I've been working on setting up automatic failover in replication topologies and saw that the results of "show slave hosts" was not being cleared out even after re-assigning the slave replica to a different master. I'm not sure the best way to do "safely" and have only found "flush status" to do the trick. However, the manual only recommends doing this for debugging.
http://dev.mysql.com/doc/refman/5.6/en/flush.html
mysql> show slave hosts;
+-----------+--------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+--------------+------+-----------+--------------------------------------+
| 2 | vmware.db1_1 | 3306 | 3 | c03e14b1-6e06-11e6-a052-000c29f50e10 |
+-----------+--------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave hosts;
Empty set (0.00 sec)
http://dev.mysql.com/doc/refman/5.6/en/flush.html
FLUSH STATUS
This option adds the current thread's session status variable values to the global values and resets the session values to zero. Some global variables may be reset to zero as well. It also resets the counters for key caches (default and named) to zero and sets
Max_used_connections to the current number of open connections. This is something you should use only when debugging a query.mysql> show slave hosts;
+-----------+--------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+--------------+------+-----------+--------------------------------------+
| 2 | vmware.db1_1 | 3306 | 3 | c03e14b1-6e06-11e6-a052-000c29f50e10 |
+-----------+--------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave hosts;
Empty set (0.00 sec)
Friday, September 2, 2016
Percona Server Error - The server quit without u[FAILED]PID file
Even thought the error I see is: The server quit without u[FAILED]PID file - I think the error is supposed to be: The server quit without updating PID file (however this tends to be a different problem and you should look in the error log)
I created some new VMs using VMWare fusion and I kept getting this error after installing Percona Server 5.6 and adding in a custom my.cnf file.
The error is vague an doesn't provide any context. I am running CentOS 6.8.
[root@vmware mysql]# /etc/init.d/mysql start
Starting MySQL (Percona Server)...The server quit without u[FAILED]PID file (/var/run/mysqld/mysqld.pid).
I saw this in the error log file:
160826 03:47:28 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
The error log also mentioned that multiple instances of MySQL might be running. I did find multiple instances of MySQL running but every time I killed them and tried to re-start I would get the problem again.
I didn't see an error code in the log file but I thought it might be a permissions problem after reading on this site:
https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/6431-percona-server-not-starting
The my.cnf file that came with the install and which works fine is very simple like this:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Since I couldn't figure out exactly what the problem was was with my settings, I went through each section and each line until mysql would start with my settings.
I had this in my.cnf:
lc_messages_dir = /usr/share/mysql
When I commented that out, MySQL started fine. I still don't know what it is with that dir that it doesn't like. After commenting it, I checked to see what Percona Server was using:
mysql> show global variables like '%lc_messages_dir%';
+-----------------+----------------------------+
| Variable_name | Value |
+-----------------+----------------------------+
| lc_messages_dir | /usr/share/percona-server/ |
+-----------------+----------------------------+
I've had this same problem but with other settings and I've followed the same approach my commenting out lines until I've figured out what the offending my.cnf entry is.
I created some new VMs using VMWare fusion and I kept getting this error after installing Percona Server 5.6 and adding in a custom my.cnf file.
The error is vague an doesn't provide any context. I am running CentOS 6.8.
[root@vmware mysql]# /etc/init.d/mysql start
Starting MySQL (Percona Server)...The server quit without u[FAILED]PID file (/var/run/mysqld/mysqld.pid).
I saw this in the error log file:
160826 03:47:28 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
The error log also mentioned that multiple instances of MySQL might be running. I did find multiple instances of MySQL running but every time I killed them and tried to re-start I would get the problem again.
I didn't see an error code in the log file but I thought it might be a permissions problem after reading on this site:
https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/6431-percona-server-not-starting
The my.cnf file that came with the install and which works fine is very simple like this:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Since I couldn't figure out exactly what the problem was was with my settings, I went through each section and each line until mysql would start with my settings.
I had this in my.cnf:
lc_messages_dir = /usr/share/mysql
When I commented that out, MySQL started fine. I still don't know what it is with that dir that it doesn't like. After commenting it, I checked to see what Percona Server was using:
mysql> show global variables like '%lc_messages_dir%';
+-----------------+----------------------------+
| Variable_name | Value |
+-----------------+----------------------------+
| lc_messages_dir | /usr/share/percona-server/ |
+-----------------+----------------------------+
I've had this same problem but with other settings and I've followed the same approach my commenting out lines until I've figured out what the offending my.cnf entry is.
Wednesday, August 31, 2016
Vagrant bug (Warning: Authentication failure. Retrying...)
I was setting up several MySQL databases using Vagrant for testing and I kept getting this Warning which was preventing me from using the VMs:
Warning: Authentication failure. Retrying...
After wasting lots of time and reading up on people's post I was able to get it working.
Work around steps:
go to this dir:
/opt/vagrant/embedded/gems/gems/vagrant-1.8.5/plugins/guests/linux/cap
vi public_key.rb (need to run as sudo)
Look for this code:
if test -f ~/.ssh/authorized_keys; then
grep -v -x -f '#{remote_path}' ~/.ssh/authorized_keys > ~/.ssh/authorized_keys.tmp
mv ~/.ssh/authorized_keys.tmp ~/.ssh/authorized_keys
Add this below that code:
chmod 0600 ~/.ssh/authorized_keys
End result will look like this:
Run "vagrant up" again
Friday, August 26, 2016
Setting up master-master or master-slave replication (without SSL)
This post has directions for setting up Master-Master replication and Master-Slave replication for testing purposes. The first part is for Master-Master replication. The second part is for Master-Slave. The directions are similar for master-master and master-slave but slightly different. These directions do not use SSL. Without SSL it is possible for hackers to sniff passwords and data transferred.
Setting up master-master replication is like setting up each master to be a slave of the other. These directions detail the steps on how to set up master-master replication between two nodes.
Edit config files for first master server (/etc/mysql/my.cnf) to include the following settings. Some of the values may vary on your server, such as the log-bin location, the binlog format (STATEMENT/MIXED instead of ROW). You may already have some of these values in your my.cnf, especially the server-id)
Note that server-id needs to be unique. auto_increment_increment and auto_increment_offset are needed to avoid primary key conflicts on auto increment columns. One of the master will have auto_increment_increment = 2, auto_increment_offset = 1 while the second master will have auto_increment_increment = 2, auto_increment_offset = 2.
First Master:
[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 2
log_slave_updates = 1
auto_increment_increment = 2
auto_increment_offset = 1
max_binlog_size = 100M
binlog_format=row
Second Master:
[mysqld]
server-id=2
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 2
log_slave_updates = 1
auto_increment_increment = 2
auto_increment_offset = 2
max_binlog_size = 100M
binlog_format=row
It may be necessary to restart the MySQL service on each master if this is the first time setting up replication on this master.
/etc/init.d/mysql restart
If MySQL fails to start, check the MySQL error log for details. Also, check to make sure that each of the log files, .info files and .index files in the above added parameters exists and have the correct file ownership of mysql.mysql.
You can fix the ownership like this:
chown -R mysql.mysql /var/log/mysql
show master status;
Copy the values to a notepad window for possible use later.
This is the user that each master-slave will use to connect to the other master.
CREATE USER 'replication'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;
Run this from the command line on each master to connect to the other master:
mysql -u replication -p -h YOURMASTERNODE
If your server doesn't have any databases on it and is a fresh MySQL install then you can skip to Step 8.
Run mysqldump on one of the Master servers (note the option of --master-data in the mysqldump command, this is needed to give you the point in time from which to start replication, this will do some locking so be careful when running it).
Also note that choosing --all-databases will dump all databases including the mysql schema, performance schema and information schema. The MySQL schema has the privileges and users which if restored on your slave it will over-write any existing users. To preserve your user permissions, it would be better to dump only the specific databases you want so as to not include the MySQL, performance_schema and information_schema using the --databases option.
mysqldump -u username -p --master-data --all-databases > dump.sql
OR
# Copy this into a bash script file on your server and execute it:
#################################################################################
MYSQL_USER=root
MYSQL_PASS=PASSWORD
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"
DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
MYSQLDUMP_OPTIONS="--master-data --routines --triggers --events --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > dump.sql
#################################################################################
mysql -u username -p < dump.sql
If you are only importing one database you would run the restore like this:
mysql -u username -p DATABASENAME < dump.sql
You are awesome. You can do this. Soon you will have two minions to do all your database replicating for you. Pat yourself on the back for making it this far and continue onto the next step.
Look at the first 25 lines or so from your dump file or if you skipped steps 5-7, use results from "SHOW MASTER STATUS" (Step 2 earlier)
Here is how you would get the first 25 lines from your dump file:
head -25 dump.sql
It should have something like this:
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Copy those values.
Log onto your second master server and use the above copied values to configure it as a slave of the first master server. This is the command you will run but replacing the appropriate values for MASTER_HOST, MASTER_USER and MASTER_PASSWORD (change the MASTER_LOG_FILE and MASTER_LOG_POS to match up with the values extracted from the dump file or from Step 2):
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE2',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
If you get errors, reset the slave and redo step 8:
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status
Make sure to check the below parameter status should be "YES" and the remaining values are appropriate.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If "Slave_SQL_Running" shows "No", then look in the "Last SQL Error" column. There might be one query causing problems with replication and you can tell MySQL to ignore it and keep going:
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> show slave status;
If you are setting up replication on a VM that was copied from another (vmware, vagrant, virtual box) system you may get this error:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
In order to resolve you will need to remove the file at /var/lib/mysql/auto.cnf on one or more of your servers and restart MySQL. MySQL will automatically regenerate that file with a unique UUID when it restarts.
Stop and Start the slave again.
Now you have setup one of the masters to be a slave of the other master. Create a table with an auto incrementing column and insert some rows on the master server and see if they are replayed on the slave. Select statements are not replayed on the slave so don't worry about those.
CREATE DATABASE IF NOT EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test`. `test_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`attribute` varchar(45) NOT NULL COMMENT 'The attribute',
`value` varchar(255) DEFAULT NULL COMMENT 'The value of the attribute',
`date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `IDX_attribute` (`attribute`)
) ENGINE=InnoDB CHARACTER SET='utf8' COLLATE='utf8_unicode_ci' COMMENT='This is a test table for testing replication';
SET NAMES utf8;
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'host name ', @@hostname, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'server id ', @@server_id, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'version ', @@version, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'TEST DATA', 'English: test - Japanese: 試し - Chinese: 试验 - Arabic: اختبار', now());
SELECT * FROM `test`.`test_table`;
show master status;
Copy the values to a notepad window.
Repeat the steps from 8,9,10 on the second master until you get replication working from both servers.
Now you have setup master-master replication! Add some more rows to the table you created in Step 11 but from the other master and make sure the primary keys are correctly off-setted and do not conflict with existing rows.
SET NAMES utf8;
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'host name ', @@hostname, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'server id ', @@server_id, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'version ', @@version, now());
SELECT * FROM `test`.`test_table`;
You should notice that the values from the first master replicated over with the odd numbers and the values written while connected to the second master have all even numbers. This is because of the offset values provided in the my.cnf file.
Setting up Master - Slave Replication
Edit config files for master server (/etc/mysql/my.cnf). Some of the values may vary on your server, such as the log-bin location, the binlog format (STATEMENT/MIXED instead of ROW). If you are adding a slave off of an existing master-master then you will need the auto_increment_increment and auto_increment_offset to be included in your my.cnf file, however, if you are only creating a master-slave without master-master-slave then that will not be needed.
[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 2
log_slave_updates = 1
#auto_increment_increment = 2
#auto_increment_offset = 1
max_binlog_size = 100M
binlog_format=row
It may be necessary to restart the MySQL service on the master if this is the first time setting up replication on this master.
/etc/init.d/mysql restart
show master status;
Copy the values to a notepad window for possible use later.
This is the user the slave will use this to connect with:
CREATE USER 'replication'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;
Edit the my.cnf for the slave(s) (/etc/mysql/my.cnf). Some of the values may vary on your server, such as the log-bin location, the binlog format (STATEMENT/MIXED instead of ROW). Each slave server-id needs to be unique.
[mysqld]
server-id=3
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 2
log_slave_updates = 1
max_binlog_size = 100M
binlog_format=row
read_only=1
Restart the MySQL service on the slave after making changes. Setting the slave to read_only is only effective against users that do not have SUPER access.
/etc/init.d/mysql restart
If MySQL fails to start, check the MySQL error log for details. Also, check to make sure that each of the log files, .info files and .index files in the above added parameters exists exist and have the correct file ownership of mysql.mysql.
If your server doesn't have any databases on it and is a fresh MySQL install then you can skip to Step 8 and use the results from Step 2 for MASTER_LOG_FILE and MASTER_LOG_FILE.
Run mysqldump on the Master server (note the option of --master-data in the mysqldump command, this is needed to give you the point in time from which to start replication, this will do some locking so be careful when running it).
Also note that choosing --all-databases will dump all databases including the mysql schema. The MySQL schema has the privileges and users which if restored on your slave it will over-write any existing users. It may be better to dump only the specific databases you want so as to not include the MySQL, performance_schema and information_schema using the --databases option.
mysqldump -u username -p --master-data --all-databases > dump.sql
OR
# Copy this into a bash script file on your server and execute it:
#################################################################################
MYSQL_USER=root
MYSQL_PASS=PASSWORD
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"
DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
MYSQLDUMP_OPTIONS="--master-data --routines --triggers --events --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > dump.sql
#################################################################################
mysql -u username -p < dump.sql
If you are only importing one database you would run the restore like this:
mysql -u username -p DATABASENAME < dump.sql
Run this from the command line:
mysql -u replication -p -h YOURMASTERNODE
Look at the first 25 lines or so from your dump file or use results from "SHOW MASTER STATUS" (if you have a MySQL instance with no database).
head -25 dump.sql
It should have something like this:
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Copy those values.
Log onto the slave server. Use the above copied values to configure the slave like this with the appropriate values for MASTER_HOST, MASTER_USER and MASTER_PASSWORD (change the MASTER_LOG_FILE and MASTER_LOG_POS to match up with the values extracted from the dump file):
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
If you get errors, reset the slave and redo step 8:
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status
Make sure to check the below parameter status should be "YES" and the remaining values are appropriate.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If "Slave_SQL_Running" shows "No", then look in the "Last SQL Error" column. There might be one query causing problems with replication and you can tell MySQL to ignore it and keep going:
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> show slave status;
If you are setting up replication on a VM that was copied from another (vmware, vagrant, virtual box) system you may get this error:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
In order to resolve you will need to remove the file at /var/lib/mysql/auto.cnf on one or more of your servers and restart MySQL. MySQL will automatically regenerate that file with a unique UUID when it restarts.
Stop and Start the slave again.
Create a table and insert some rows on the master server and see if they are replayed on the slave. Select statements are not replayed on the slave so don't worry about those.
-- SQL to run on the MASTER:
CREATE DATABASE IF NOT EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test`. `test_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`attribute` varchar(45) NOT NULL COMMENT 'The attribute',
`value` varchar(255) DEFAULT NULL COMMENT 'The value of the attribute',
`date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `IDX_attribute` (`attribute`)
) ENGINE=InnoDB CHARACTER SET='utf8' COLLATE='utf8_unicode_ci' COMMENT='This is a test table for testing replication';
SET NAMES utf8;
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'host name ', @@hostname, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'server id ', @@server_id, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'version ', @@version, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'TEST DATA', 'English: test - Japanese: 試し - Chinese: 试验 - Arabic: اختبار', now());
-- SQL to run on both the MASTER and SLAVE to verify replication worked:
SELECT * FROM `test`.`test_table`;
Part 1: Master-Master Replication
Setting up master-master replication is like setting up each master to be a slave of the other. These directions detail the steps on how to set up master-master replication between two nodes.
Step 1: Setup config file for each master master
Edit config files for first master server (/etc/mysql/my.cnf) to include the following settings. Some of the values may vary on your server, such as the log-bin location, the binlog format (STATEMENT/MIXED instead of ROW). You may already have some of these values in your my.cnf, especially the server-id)
Note that server-id needs to be unique. auto_increment_increment and auto_increment_offset are needed to avoid primary key conflicts on auto increment columns. One of the master will have auto_increment_increment = 2, auto_increment_offset = 1 while the second master will have auto_increment_increment = 2, auto_increment_offset = 2.
First Master:
[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 2
log_slave_updates = 1
auto_increment_increment = 2
auto_increment_offset = 1
max_binlog_size = 100M
binlog_format=row
Second Master:
[mysqld]
server-id=2
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 2
log_slave_updates = 1
auto_increment_increment = 2
auto_increment_offset = 2
max_binlog_size = 100M
binlog_format=row
It may be necessary to restart the MySQL service on each master if this is the first time setting up replication on this master.
/etc/init.d/mysql restart
If MySQL fails to start, check the MySQL error log for details. Also, check to make sure that each of the log files, .info files and .index files in the above added parameters exists and have the correct file ownership of mysql.mysql.
You can fix the ownership like this:
chown -R mysql.mysql /var/log/mysql
Step 2: Check the status of the first master:
show master status;
Copy the values to a notepad window for possible use later.
Step 3: Create a replication user on both master servers
CREATE USER 'replication'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;
Step 4: Test that the replication user can connect to each server
mysql -u replication -p -h YOURMASTERNODE
Step 5: Create a dumpfile from the master
Run mysqldump on one of the Master servers (note the option of --master-data in the mysqldump command, this is needed to give you the point in time from which to start replication, this will do some locking so be careful when running it).
Also note that choosing --all-databases will dump all databases including the mysql schema, performance schema and information schema. The MySQL schema has the privileges and users which if restored on your slave it will over-write any existing users. To preserve your user permissions, it would be better to dump only the specific databases you want so as to not include the MySQL, performance_schema and information_schema using the --databases option.
mysqldump -u username -p --master-data --all-databases > dump.sql
OR
# Copy this into a bash script file on your server and execute it:
#################################################################################
MYSQL_USER=root
MYSQL_PASS=PASSWORD
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"
DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
MYSQLDUMP_OPTIONS="--master-data --routines --triggers --events --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > dump.sql
#################################################################################
Step 6: Restore dumpfile from the master on the slave
If you are only importing one database you would run the restore like this:
mysql -u username -p DATABASENAME < dump.sql
Step 7: Give yourself a motivational pep talk
Step 8: Configure Slave Process
Here is how you would get the first 25 lines from your dump file:
head -25 dump.sql
It should have something like this:
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Copy those values.
Log onto your second master server and use the above copied values to configure it as a slave of the first master server. This is the command you will run but replacing the appropriate values for MASTER_HOST, MASTER_USER and MASTER_PASSWORD (change the MASTER_LOG_FILE and MASTER_LOG_POS to match up with the values extracted from the dump file or from Step 2):
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE2',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Step 9: Start Slave Process on first master
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
If you get errors, reset the slave and redo step 8:
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
Step 10: Slave Status
mysql> show slave status
Make sure to check the below parameter status should be "YES" and the remaining values are appropriate.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If "Slave_SQL_Running" shows "No", then look in the "Last SQL Error" column. There might be one query causing problems with replication and you can tell MySQL to ignore it and keep going:
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> show slave status;
If you are setting up replication on a VM that was copied from another (vmware, vagrant, virtual box) system you may get this error:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
In order to resolve you will need to remove the file at /var/lib/mysql/auto.cnf on one or more of your servers and restart MySQL. MySQL will automatically regenerate that file with a unique UUID when it restarts.
Stop and Start the slave again.
Step 11: Test the replication
CREATE DATABASE IF NOT EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test`. `test_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`attribute` varchar(45) NOT NULL COMMENT 'The attribute',
`value` varchar(255) DEFAULT NULL COMMENT 'The value of the attribute',
`date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `IDX_attribute` (`attribute`)
) ENGINE=InnoDB CHARACTER SET='utf8' COLLATE='utf8_unicode_ci' COMMENT='This is a test table for testing replication';
SET NAMES utf8;
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'host name ', @@hostname, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'server id ', @@server_id, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'version ', @@version, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'TEST DATA', 'English: test - Japanese: 試し - Chinese: 试验 - Arabic: اختبار', now());
SELECT * FROM `test`.`test_table`;
You should notice that the id column has all odd numbers because there were written while connected to the first master.
Step 12: Check the status of the second master
Copy the values to a notepad window.
Step 13: Repeat slave process on second master
Step 14: Test auto-increment offsets
SET NAMES utf8;
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'host name ', @@hostname, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'server id ', @@server_id, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'version ', @@version, now());
SELECT * FROM `test`.`test_table`;
You should notice that the values from the first master replicated over with the odd numbers and the values written while connected to the second master have all even numbers. This is because of the offset values provided in the my.cnf file.
Part 2: Master-Slave Replication
Setting up Master - Slave Replication
Step 1: Setup config file for master
[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 2
log_slave_updates = 1
#auto_increment_increment = 2
#auto_increment_offset = 1
max_binlog_size = 100M
binlog_format=row
It may be necessary to restart the MySQL service on the master if this is the first time setting up replication on this master.
/etc/init.d/mysql restart
Step 2: Check that status of the master
Copy the values to a notepad window for possible use later.
Step 3: Create a replication user on the master server
CREATE USER 'replication'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;
Step 4: Setup config file for slave(s)
Edit the my.cnf for the slave(s) (/etc/mysql/my.cnf). Some of the values may vary on your server, such as the log-bin location, the binlog format (STATEMENT/MIXED instead of ROW). Each slave server-id needs to be unique.
[mysqld]
server-id=3
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 2
log_slave_updates = 1
max_binlog_size = 100M
binlog_format=row
read_only=1
Restart the MySQL service on the slave after making changes. Setting the slave to read_only is only effective against users that do not have SUPER access.
/etc/init.d/mysql restart
If MySQL fails to start, check the MySQL error log for details. Also, check to make sure that each of the log files, .info files and .index files in the above added parameters exists exist and have the correct file ownership of mysql.mysql.
Step 5: Create a dumpfile from the master
Run mysqldump on the Master server (note the option of --master-data in the mysqldump command, this is needed to give you the point in time from which to start replication, this will do some locking so be careful when running it).
Also note that choosing --all-databases will dump all databases including the mysql schema. The MySQL schema has the privileges and users which if restored on your slave it will over-write any existing users. It may be better to dump only the specific databases you want so as to not include the MySQL, performance_schema and information_schema using the --databases option.
mysqldump -u username -p --master-data --all-databases > dump.sql
OR
# Copy this into a bash script file on your server and execute it:
#################################################################################
MYSQL_USER=root
MYSQL_PASS=PASSWORD
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"
DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
MYSQLDUMP_OPTIONS="--master-data --routines --triggers --events --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > dump.sql
#################################################################################
Step 6: Restore dumpfile from the master on the slave
If you are only importing one database you would run the restore like this:
mysql -u username -p DATABASENAME < dump.sql
Step 7: Connection Testing (Slave to Master)
mysql -u replication -p -h YOURMASTERNODE
Step 8: Configure Slave Process
head -25 dump.sql
It should have something like this:
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Copy those values.
Log onto the slave server. Use the above copied values to configure the slave like this with the appropriate values for MASTER_HOST, MASTER_USER and MASTER_PASSWORD (change the MASTER_LOG_FILE and MASTER_LOG_POS to match up with the values extracted from the dump file):
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Step 9: Start Slave
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
If you get errors, reset the slave and redo step 8:
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
Step 10: Slave Status
Make sure to check the below parameter status should be "YES" and the remaining values are appropriate.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If "Slave_SQL_Running" shows "No", then look in the "Last SQL Error" column. There might be one query causing problems with replication and you can tell MySQL to ignore it and keep going:
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> show slave status;
If you are setting up replication on a VM that was copied from another (vmware, vagrant, virtual box) system you may get this error:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
In order to resolve you will need to remove the file at /var/lib/mysql/auto.cnf on one or more of your servers and restart MySQL. MySQL will automatically regenerate that file with a unique UUID when it restarts.
Stop and Start the slave again.
Step 11: Test the replication
-- SQL to run on the MASTER:
CREATE DATABASE IF NOT EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test`. `test_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`attribute` varchar(45) NOT NULL COMMENT 'The attribute',
`value` varchar(255) DEFAULT NULL COMMENT 'The value of the attribute',
`date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `IDX_attribute` (`attribute`)
) ENGINE=InnoDB CHARACTER SET='utf8' COLLATE='utf8_unicode_ci' COMMENT='This is a test table for testing replication';
SET NAMES utf8;
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'host name ', @@hostname, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'server id ', @@server_id, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'version ', @@version, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'TEST DATA', 'English: test - Japanese: 試し - Chinese: 试验 - Arabic: اختبار', now());
-- SQL to run on both the MASTER and SLAVE to verify replication worked:
SELECT * FROM `test`.`test_table`;
Subscribe to:
Posts (Atom)