Cloning MySQL Grants

I needed to clone some grants when migrating a customer system recently. There is probably a more elegant way of going this but it was a good starting point.

Advertisements
Cloning MySQL Grants

Talks at Percona Live 2014

I seem to have ended up presenting 3 different talks at Percona Live in Santa Clara this year.

Automatically Deployed MySQL Geo-Clustering for Every Situation with Continuent Tungsten along with Jeff Mace

Avoiding pain when running MySQL in the cloud

Why puppet can save your sanity

As usual Continuent are sponsoring the conference as we have a big attendance from the team with 17 Continuent Sessions.

 

Talks at Percona Live 2014

Quick Galera Setup

Configuring the nodes

3 CentOS nodes with a minimal install

Galera1 :  192.168.0.231
Galera2 :  192.168.0.232
Galera3 :  192.168.0.233

On each node

rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
yum install Percona-XtraDB-Cluster-server
yum install Percona-XtraDB-Cluster-client
yum install xtrabackup
mkdir -p /mnt/data
mysql_install_db –datadir=/mnt/data –user=mysql

On each node put the following /etc/my.cnf

Galera1

[mysqld_safe]
wsrep_urls=gcomm://192.168.0.231:4567,gcomm://192.168.0.232:4567,gcomm://192.168.0.233:4567,gcomm://

[mysqld]
datadir=/mnt/data
user=mysql

binlog_format=ROW

wsrep_provider=/usr/lib64/libgalera_smm.so

wsrep_slave_threads=2
wsrep_cluster_name=galeracluster
wsrep_sst_method=rsync
wsrep_node_name=node1

innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
log-bin=mysqld-bin
server-id=1

Galera2

[mysqld_safe]
wsrep_urls=gcomm://192.168.0.231:4567,gcomm://192.168.0.232:4567,gcomm://192.168.0.233:4567,gcomm://

[mysqld]
datadir=/mnt/data
user=mysql

binlog_format=ROW

wsrep_provider=/usr/lib64/libgalera_smm.so

wsrep_slave_threads=2
wsrep_cluster_name=galeracluster
wsrep_sst_method=rsync
wsrep_node_name=node2

innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
log-bin=mysqld-bin
server-id=2

Galera3

[mysqld_safe]
wsrep_urls=gcomm://192.168.0.231:4567,gcomm://192.168.0.232:4567,gcomm://192.168.0.233:4567,gcomm://

[mysqld]
datadir=/mnt/data
user=mysql

binlog_format=ROW

wsrep_provider=/usr/lib64/libgalera_smm.so

wsrep_slave_threads=2
wsrep_cluster_name=galeracluster
wsrep_sst_method=rsync
wsrep_node_name=node3

innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
log-bin=mysqld-bin
server-id=3

Startup the cluster

on Galera1

mysqld_safe &

On Galera2 and 3

mysqld_safe &

Look at the mysql errorlog in /mnt/data for the following message. It means all 3 nodes are synced

130204  9:41:50 [Note] WSREP: Quorum results:
        version    = 2,
        component  = PRIMARY,
        conf_id    = 4,
        members    = 3/3 (joined/total),
        act_id     = 1,
        last_appl. = 0,
        protocols  = 0/4/2 (gcs/repl/appl),
        group UUID = 52e67407-6eae-11e2-0800-8eebc3f6cc80

Quick Galera Setup

Another interesting mysql feature

I’ve been having problems with replication on a dev machine today because of a rather annoying feature. It appears it is possible to have a comma in a database name so if you use the command
replicate_do_db = db1,db2
It actually thinks you are trying to replicate the database ‘db1,db2’ which obviously doesn’t exist
They have to be added on 2 separate lines for it to work
replicate_do_db = db1
replicate_do_db = db2
Annoyingly the show slave statement actually uses commas to separate the databases
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: dev1
                  Master_User: repuser
                  Master_Port: 3103
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000133
          Read_Master_Log_Pos: 3067355
               Relay_Log_File: peripheral_3105-relay-bin.000214
                Relay_Log_Pos: 407551
        Relay_Master_Log_File: mysql-bin.000133
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: db1,db2
          Replicate_Ignore_DB:

It’s documented here in the manual 



Warning
To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.

Another interesting mysql feature

First play with Percona XtraDB Cluster

I wanted to try out XtraDB cluster as I am keen on using this technology and have played with Galera before. I have managed to set up a working 3 cluster system running on a single Centos 6 virtual machine using the instructions combined from the 2 following pages

At the end of this you should end up with a 3 node cluster running on ports 4000, 5000 and 6000.
Step 1 – Configure the Machine
Standard Centos 6 build with all the up to date packages installed – the host has an ip of 192.168.1.233 so all the config files will need to be changed to match your IP address
Added the following Percona Repos

And installed XtraDB

yum install Percona-XtraDB-Cluster-server Percona-XtraDB-Cluster-client

Created the 3 dir’s to hold the data directories

mkdir -p /data/bench/d1
mkdir -p /data/bench/d2
mkdir -p /data/bench/d2

Step 2 – Set up MySQL

Created the 3 my.cnf files

vi /etc/my.4000.cnf

[mysqld]
gdb
datadir=/data/bench/d1
port = 4000
socket=/tmp/mysql.4000.sock
user=root
binlog_format=ROW
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://
wsrep_provider_options = “gmcast.listen_addr=tcp://0.0.0.0:4010; “
wsrep_sst_receive_address=192.168.1.233:4020
wsrep_slave_threads=2
wsrep_cluster_name=trimethylxanthine
wsrep_sst_method=rsync
wsrep_node_name=node4000
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2



vi /etc/my.5000.cnf

[mysqld]
gdb
datadir=/data/bench/d2
port = 5000
socket=/tmp/mysql.5000.sock
user=root
binlog_format=ROW
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.233:4010
wsrep_provider_options = “gmcast.listen_addr=tcp://0.0.0.0:5010; “
wsrep_sst_receive_address=192.168.1.233:5020
wsrep_slave_threads=2
wsrep_cluster_name=trimethylxanthine
wsrep_sst_method=rsync
wsrep_node_name=node5000
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
vi /etc/my.6000.cnf
[mysqld]
gdb
datadir=/data/bench/d3
port = 6000
socket=/tmp/mysql.6000.sock
user=root
binlog_format=ROW
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.233:4010
wsrep_provider_options = “gmcast.listen_addr=tcp://0.0.0.0:6010; “
wsrep_sst_receive_address=192.168.1.233:6020
wsrep_slave_threads=2
wsrep_cluster_name=trimethylxanthine
wsrep_sst_method=rsync
wsrep_node_name=node6000
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
Install the mysql default tables
mysql_install_db –datadir=/data/bench/d1
mysql_install_db –datadir=/data/bench/d2
mysql_install_db –datadir=/data/bench/d3
Step 3 – Starting the Cluster
In this case 4000 has to be started first
mysqld_safe –defaults-file=/etc/my.4000.cnf &
Once 4000 is up 5000 and 6000 can be started
mysqld_safe –defaults-file=/etc/my.5000.cnf &
mysqld_safe –defaults-file=/etc/my.6000.cnf &
The log for 4000 should contain messages similar to below
120115 16:35:52 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 0)
120115 16:35:52 [Note] WSREP: Member 0 (node4000) synced with group.
120115 16:35:52 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
120115 16:35:52 [Note] WSREP: Synchronized with group, ready for connections
120115 16:35:52 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
120115 16:35:53 [Note] WSREP: 1 (node5000): State transfer from 0 (node4000) complete.
120115 16:35:53 [Note] WSREP: Member 1 (node5000) synced with group.
120115 16:36:25 [Note] WSREP: 0 (node4000): State transfer to 2 (node6000) complete.
120115 16:36:25 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 0)
120115 16:36:25 [Note] WSREP: Member 0 (node4000) synced with group.
120115 16:36:25 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
120115 16:36:25 [Note] WSREP: Synchronized with group, ready for connections
120115 16:36:25 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
120115 16:36:27 [Note] WSREP: 2 (node6000): State transfer from 0 (node4000) complete.
120115 16:36:27 [Note] WSREP: Member 2 (node6000) synced with group.
As far as I can see so far the important messages are WSREP: Member 1 (node5000) synced with group. and Member 2 (node6000) synced with group.

Step 4 – Testing the cluster
Create a table in 4000
mysql -P4000 -h 127.0.0.1 -e”create table test.a (a int)”;
and it should be created in 5000 and 6000 as well
[root@percona2 /]# mysql -P5000 -h 127.0.0.1 test -e”show tables”;
+—————-+
| Tables_in_test |
+—————-+
| a              |
+—————-+
[root@percona2 /]# mysql -P6000 -h 127.0.0.1 test -e”show tables”;
+—————-+
| Tables_in_test |
+—————-+
| a              |
+—————-+
[root@percona2 /]# 

First play with Percona XtraDB Cluster