I'm transitioning from one mysql server to another, in production use,
by setting the new server up as a slave, so that when it's time to
switch, we'll have very little downtime. Then we can turn off
replication and the new slave will become the new production server.
I set up a replication slave, tested it, and everything was fine.
Then I stopped it, reset slave, and used it for something else.
Now I want to make it a slave again, but it stops partway through
catching up on the binary logs, with this error:
Operation CREATE USER failed for 'replication'@'[host]'
(where [host] is the slave's hostname)
Here is the procedure I followed to make it a slave:
1. drop database our_main_db;
create database our_main_db;
2. Brought over the most recent mysqldump from the master,
which was created from a command like this:
mysqldump --flush-logs --master-data=2 -uuser -ppasswd our_main_db
... and fed the dump's contents into the slave-to-be server.
3. Using the log file name and position from the master data in that
dump file, issed a change master statement:
CHANGE MASTER TO
MASTER_HOST='masterserver.domain.name',
MASTER_USER='replication',
MASTER_PASSWORD='replicationuserpassword',
MASTER_LOG_FILE='binlog.000011',
MASTER_LOG_POS=98;
START SLAVE;
... everything was running fine for an hour or so, and the slave
caught up through several days worth of logs from the master, but then
it stopped with this error:
Last_Errno: 1396
Last_Error: Error 'Operation CREATE USER failed for
'replication'@'[host]'' on query. Default database: 'mysql'.
Query: 'create user 'replication'@'[host]' identified by
'replicationuserpassword''
(again, [host] is the slave's own hostname).
I checked the mysql.user table and found that the [EMAIL PROTECTED]
user *did* exist. So I removed it from the table, then tried to
restart replication ... and got the same error.
So then I went to the binary log on the master and, using mysqlbinlog,
found the exact create user statement, and tried to run it by hand on
the slave, which looked like this:
create user 'replication'@'[host]' identified by 'replicationuserpasswd';
I tried running that by hand on the slave server (from the mysql root user)
and got the error again:
mysql> create user 'replication'@'[host]' identified by 'replicationuserpasswd';
ERROR 1396 (HY000): Operation CREATE USER failed for 'replication'@'[host]'
... I "solved" the problem by adding slave_skip_errors=1396 to my.cnf
and restarting the slave server. It was able to pick up replication
and is now caught up with the master and seems to be fine. However,
1. I don't understand what caused the problem
2. I fear that after I un-slave it (we're planning to switch masters)
I still won't be able to create users on this new server.
-- Cos (Ofer Inbar) -- [EMAIL PROTECTED]
"So you're one of Jehovah's Witnesses. I'm Cthulhu's defence lawyer.
prepare for cross-questioning" -- Tanuki, on alt.sysadmin.recovery
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
