MySQL patches by Codership

Migrate existing database on demo install

Asked by Christopher Gorge A. Marges

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-5.1.53-galera-0.7.6-i386/

Here are the steps we did:
1) Rename /mysql-5.1.53-galera-0.7.6-i386/mysql/var to /mysql-5.1.53-galera-0.7.6-i386/mysql/var0
2) Copy the data files to /mysql-5.1.53-galera-0.7.6-i386/mysql/var (we did a tar on the existing folder to preserve the structure)
3) Remove the ib_logfile*
4) Edit /root/mysql-5.1.53-galera-0.7.6-i386/mysql/etc/my.cnf

# Address for incoming client connections. Autodetect by default.
wsrep_node_incoming_address=10.0.0.31

# 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_receive_address=10.0.0.31

# 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_auth=root:root

(The old mysql database has a root user with the password root)

5) Start galera on one server

# mysql-5.1.53-galera-0.7.6-i386/mysql-galera -g gcomm:// start
Starting mysqld instance with data dir /root/mysql-5.1.53-galera-0.7.6-i386/mysql/var and listening at port 3306 and socket /root/mysql-5.1.53-galera-0.7.6-i386/mysql/var/mysqld.sock................................................................. Done (PID:8071)
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/mysqld/mysqld.sock' (2)

# mysql -h 10.0.0.31 -u root -proot -A
ERROR 1045 (28000): Access denied for user 'root'@'teletraan1.apollo.com.ph' (using password: YES)

# 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.apollo.com.ph teletraan1
10.0.0.31 teletraan1.apollo.com.ph teletraan1
10.0.0.32 teletraan2.apollo.com.ph 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-external-locking' instead.
/root/mysql-5.1.53-galera-0.7.6-i386/mysql/libexec/mysqld: Table 'mysql.plugin' doesn't exist
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/mysql-5.1.53-galera-0.7.6-i386/galera/lib/libmmgalera.so'
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-5.1.53-galera-0.7.6-i386/mysql/var//grastate.dat
110523 11:11:14 [Warning] WSREP: GALERA state restore failed
110523 11:11:14 [Note] WSREP: Found stored state: 00000000-0000-0000-0000-000000000000:-1
110523 11:11:14 [Note] WSREP: Configured state: 00000000-0000-0000-0000-000000000000:-1
110523 11:11:14 [Note] WSREP: gcomm: connecting to group 'my_wsrep_cluster', peer ''
110523 11:11:14 [Note] WSREP: GMCast::handle_stable_view: view(view_id(PRIM,5f73bf82-852d-11e0-0800-4213c85d89d9,1) memb {5f73bf82-852d-11e0-0800-4213c85d89d9, } joined {} left {} pa$
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-5.1.53-galera-0.7.6-i386/mysql/libexec/mysqld: ready for connections.
Version: '5.1.53' socket: '/root/mysql-5.1.53-galera-0.7.6-i386/mysql/var/mysqld.sock' port: 3306 wsrep_0.7.6
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-852d-11e0-0800-8ae6fd65cfea
110523 11:11:14 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 5fc102e6-852d-11e0-0800-2366f6366de7
110523 11:11:14 [Note] WSREP: STATE EXCHANGE: sent state msg: 5fc102e6-852d-11e0-0800-2366f6366de7
110523 11:11:14 [Note] WSREP: STATE EXCHANGE: got state msg: 5fc102e6-852d-11e0-0800-2366f6366de7 from 0 (teletraan1)
110523 11:11:14 [Note] WSREP: Quorum results:
        PRIMARY,
        act_id = 0,
        conf_id = 0,
        last_appl. = 0,
        protocol = 0,
        group UUID = 5fc0ca68-852d-11e0-0800-8ae6fd65cfea
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-852d-11e0-0800-8ae6fd65cfea, conf# 1: Primary, number of nodes: 1, my index: 0, first seqno: 1
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
For:
MySQL patches by Codership Edit question
Assignee:
No assignee Edit question
Solved by:
Alex Yurchenko
Solved:
Last query:
Last reply:
Revision history for this message
Best Alex Yurchenko (ayurchen) said : 		#1

Hi Christopher,

There can be a number of reasons why you had these connectivity/authentication issues. None of them is related to Galera or wsrep patch, they are all stock mysql-related. I'll try to outline them below.

1.
Waiting for wsrep_ready.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

myslq-galera startup script from the demo package by default uses the password for a bundled database, so when you changed the database, old root password no longer worked. To make mysql-galera script to use another password, set ROOT_PSWD env. variable to that value. The error by itself is harmless, it does not indicate any malfunction in the server. The logs you supplied show that everything was ok.

2.
# mysql -u root -proot -A
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

as you can see from
# mysql-5.1.53-galera-0.7.6-i386/mysql-galera -g gcomm:// start
Starting mysqld instance with data dir /root/mysql-5.1.53-galera-0.7.6-i386/mysql/var and listening at port 3306 and socket /root/mysql-5.1.53-galera-0.7.6-i386/mysql/var/mysqld.sock................................................................. Done (PID:8071)

mysql-galera script started mysqld with the socket at /root/mysql-5.1.53-galera-0.7.6-i386/mysql/var/mysqld.sock while your client tries to access it at /var/run/mysqld/mysqld.sock which is either a compiled-in default or specified in a system-wide my.cnf file.

3.
# mysql -h 10.0.0.31 -u root -proot -A
ERROR 1045 (28000): Access denied for user 'root'@'teletraan1.apollo.com.ph' (using password: YES)

# mysql -h 127.0.0.1 -u root -proot -A
Welcome to the MySQL monitor. Commands end with ; or \g.

mysqld uses a somewhat tricky authentication scheme, where one user id can have several passwords depending on the connection source (see mysql.user table). E.g. root connection may be allowed only through unix socket or only from a localhost. In the first example mysqld saw you as connecting from another machine, while in the second the source of connection was local host.

Hope this helps.

Regards,
Alex

Revision history for this message
Christopher Gorge A. Marges (gorge) said : 		#2

Thanks Alex Yurchenko, that solved my question.