MySQL Migrate Users and Grants to Another MySQL Database

Hi,

Sometimes we need to change our database. In this case we have to migrate users and their grants to the new database server.

Here is a procedure for this issue

1- Prepare grant sqls

[root@olddb ~]# mysql -uroot -pmypass -s -N << EOF > showgr.sql
select distinct CONCAT('show grants for "', user, '"@"', host, '";') as query from mysql.user;
EOF

showgr.sql looks like :

[root@olddb ~]# head showgr.sql
show grants for "USER1"@"xxx.xxx.xxx.xxx";
show grants for "USER2"@"xxx.xxx.xxx.xxx";
show grants for "USER3"@"xxx.xxx.xxx.xxx";

Let’s create grant sqls running showgr.sql against to the old database.

[root@olddb ~]# mysql -uroot -N -ppmypass -s -r < showgr.sql > grant.sql

Unfortunately MySQL creates grant sql without “;” at the end. To add “;” at the end of every line we use sed.

[root@olddb ~]# more grant.sql |sed 's/$/;/g' > grants.sql

2- SCP file and import it!

Now we have a list of grants for importing too the new one.

[root@olddb ~]# scp grants.sql user@newdb:/tmp/

Now  we can import grant file on the new host

[root@newdb ~]# mysql -uroot -pmypass  < grants.sql

Good Luck.

MySQL Error 1236

Hi,

When mysqld crashes before flushes the log , you may get this error and replication can not resume.

This can be shown like when “show slave status” performed:

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

For solution :

Suppose that you have a Master-Master Replication on HostA and HostB.

On HostA:

1- stop slave;

2-reset master;

3-reset slave;

On HostB:

1- stop slave;

2-reset master;

3-reset slave;

After all you can set up replication between them using this method https://hsnyrd.wordpress.com/2013/07/15/setting-up-master-slave-mysql-replication/

Good Luck.

Setting up Master – Slave and Master – Master MySQL Replication

If you are using MySQL as a database, one of the best solution is using MySQL Replication for High Availability.

Here is a short procedure for setting up replication :

  • Set one of server’s “server-id” parameter to any integer value different from the slave one.

For master mysql database :

# vi /etc/my.cnf

….

server-id     = 5   # This server’s id is 5

….

For slave mysql database

# vi /etc/my.cnf

….

server-id     = 6   # This server’s id is 6

….

  • Restart both MySQL servers for reload my.cnf files.

If you have mysql script under /etc/init.d/ , you can start and stop db servers running “service mysql stop” and “service mysql start” commands.

  • Login slave MySQL database server and run “fake” change master command. Please fill the MASTER_HOST information with the master host’s hostname.

mysql> CHANGE MASTER TO MASTER_HOST=’hostname’, MASTER_USER=’rep_user’,
-> MASTER_PASSWORD=’rep_pass’, MASTER_PORT=3306;

  • Now we will migrate the data from the master host to the slave one. First export full master database to alldb.sql using below command.

# mysqldump -uroot -p –all-databases –single-transaction  –master-data=1  > alldb.sql

  • scp the dump file (alldb.sql) to the slave node from the master node.

# scp alldb.sql slave_node:

  • Import this file to the slave mysql database.

# mysql -uroot -p < alldb.sql

  • When import operation finishes, you can logon the slave database and enable slave mode running :

mysql> start slave;

  • You can check running “show slave status \G” on the slave server. Both Slave_IO_Running and Slave_SQL_Running must be run. (“Yes” indicates that it is running)

mysql> show slave status \G

…..

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

….

  • Now test time !!

create a table to the master database under test schema and see that it comes to slave database..

If you want to set up Master-Master Replication , you perform a CHANGE MASTER command on the first host taking MASTER_LOG_FILE and MASTER_LOG_POS values from the slave.

On Slave:

mysql> show master status \G
*************************** 1. row ***************************
File: db2-bin.000004
Position: 588484588
Binlog_Do_DB:
Binlog_Ignore_DB:

On Master:

CHANGE MASTER TO
MASTER_HOST=’db2′,
MASTER_USER=rep_user’,
MASTER_PASSWORD=’rep_pass’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’db2-bin.000004′,
MASTER_LOG_POS=588484588;

Good Luck 🙂

MySQL Size Queries

Here is some useful queries for finding MySQL database , schema or table size information.

  • Size of Database group by schema Name :

select table_schema "Schema Name",
sum(data_length + index_length)/1024/1024 "Data Base Size in MB"
from information_schema.tables group by table_schema;

  • Size of Schema group by table names :

select table_name, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
from information_schema.tables where table_schema ='<Schema Name>';

  • All Database Size :

select sum( data_length + index_length) / 1024 / 1024 "Data Base Size in MB"
from information_schema.tables;