Migrate existing database on demo install
We were able to install the demo on two servers and would like to see if we can just copy over the data files of an existing MySQL 5.1.x database to the directory where galera is.
Galera was installed on /root/mysql-
Here are the steps we did:
1) Rename /mysql-
2) Copy the data files to /mysql-
3) Remove the ib_logfile*
4) Edit /root/mysql-
# Address for incoming client connections. Autodetect by default.
wsrep_node_
# Address on THIS node to receive SST at. DON'T SET IT TO DONOR ADDRESS!!!
# (SST method dependent. Defaults to the first IP of the first interface)
wsrep_sst_
# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:<root password>
wsrep_sst_
(The old mysql database has a root user with the password root)
5) Start galera on one server
# mysql-5.
Starting mysqld instance with data dir /root/mysql-
Waiting for wsrep_ready.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
This is strange since we can connect to the server. The server is running.
# mysql -u root -proot -A
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/
# mysql -h 10.0.0.31 -u root -proot -A
ERROR 1045 (28000): Access denied for user 'root'@
# mysql -h 127.0.0.1 -u root -proot -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.53 wsrep_0.7.6
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Other details:
/etc/hosts
127.0.0.1 localhost
#127.0.1.1 teletraan1.
10.0.0.31 teletraan1.
10.0.0.32 teletraan2.
# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts
Contents of teletraan1.err:
teletraan1.err
110523 11:10:10 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-
/root/mysql-
110523 11:10:10 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
110523 11:10:10 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
110523 11:10:34 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
110523 11:11:13 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
110523 11:11:14 InnoDB: Started; log sequence number 0 403980
110523 11:11:14 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
110523 11:11:14 [Note] WSREP: wsrep_load(): loading provider library '/root/
110523 11:11:14 [Note] WSREP: wsrep_load(): provider loaded succesfully.
110523 11:11:14 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. Created with MySQL 50022, now running 50153. Please use mysql_upgrade to fix this error.
110523 11:11:14 [ERROR] mysql.user has no `Event_priv` column at position 29
110523 11:11:14 [ERROR] Cannot open mysql.event
110523 11:11:14 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
110523 11:11:14 [Note] WSREP: start replication
110523 11:11:14 [ERROR] WSREP: could not open state file: /root/mysql-
110523 11:11:14 [Warning] WSREP: GALERA state restore failed
110523 11:11:14 [Note] WSREP: Found stored state: 00000000-
110523 11:11:14 [Note] WSREP: Configured state: 00000000-
110523 11:11:14 [Note] WSREP: gcomm: connecting to group 'my_wsrep_cluster', peer ''
110523 11:11:14 [Note] WSREP: GMCast:
110523 11:11:14 [Note] WSREP: gcomm: connected
110523 11:11:14 [Note] WSREP: Changing maximum message size 0 -> 32636
110523 11:11:14 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
110523 11:11:14 [Note] WSREP: Opened channel 'my_wsrep_cluster'
110523 11:11:14 [Note] WSREP: Successfully opened GCS connection to my_wsrep_cluster
110523 11:11:14 [Note] /root/mysql-
Version: '5.1.53' socket: '/root/
110523 11:11:14 [Note] WSREP: New COMPONENT: primary = yes, my_idx = 0, memb_num = 1
110523 11:11:14 [Note] WSREP: Starting new group from scratch: 5fc0ca68-
110523 11:11:14 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 5fc102e6-
110523 11:11:14 [Note] WSREP: STATE EXCHANGE: sent state msg: 5fc102e6-
110523 11:11:14 [Note] WSREP: STATE EXCHANGE: got state msg: 5fc102e6-
110523 11:11:14 [Note] WSREP: Quorum results:
PRIMARY,
act_id = 0,
conf_id = 0,
last_appl. = 0,
protocol = 0,
group UUID = 5fc0ca68-
110523 11:11:14 [Note] WSREP: Flow-control interval: [0, 1]
110523 11:11:14 [Note] WSREP: Restored state OPEN -> JOINED (0)
110523 11:11:14 [Note] WSREP: New cluster view: group UUID: 5fc0ca68-
110523 11:11:14 [Note] WSREP: Member 0 (teletraan1) synced with group.
110523 11:11:14 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
The reason why we want to do this is because we wanted to minimize the downtime in case we want to convert our old database from mysql to mysql-galera. We thought that since the database files are from 5.1.x then it should be compatible when copied to galera. Please advice on what we may have missed. Thanks.
Question information
- Language:
- English Edit question
- Status:
- Solved
- Assignee:
- No assignee Edit question
- Solved by:
- Alex Yurchenko
- Solved:
- Last query:
- Last reply: