dbaas migraton importing to dbaas

This is how I go about doing MySQL —> DBaaS migrations. 
*** Please read this and understand this and do not blindly copy/paste. This is fairly straight forward but there are plenty of account specific items within the mysql commands that need to be substituted based on the DBaaS instance you are migrating from.  ***
*** If you are unsure about this, consult with your technical lead on the team, they can help figure this out for you ***
*** For long running imports, feel free to put this in a screen session *** 
This might not be a good fit for adding additional databases or importing databases that already exist. This is really best fit for initial set up. If the database exists, the create database will error, but the import will drop the database and import the database in its place overwriting the data currently there.
Set up DBaaS instance via the server and enable root. (won’t show how to do this, its fairly easy and straight forward using https://pitchfork.eco.rackspace.com/databases/ ).
1. Do holland dump: 
Command
holland bk
2. Create databases via command: (before anyone starts gripping about passwords via the command line, I disable root once the initial import/setup is finished)
** Supper important, if you notice I exclude the mysql database so we don’t break stuff… we will get to user imports in a bit ** 
Command:
for database in $(grep -vw “mysql” /var/lib/mysqlbackup/default/newest/backup_data/MANIFEST.txt | awk ‘{print $1}’); do echo “Creating database $database” && mysql -h 67c87bb854cc70dde5bd6717a3a9b239f92aa1d1.rackspaceclouddb.com -u root -pspc9UqusADzHVy6mTmpDZMQeY36zQjNy3Cge -e “create database $database”; done
Output:
Creating database database1
Creating database database10
Creating database database2
Creating database database3
Creating database database4
Creating database database5
Creating database database6
Creating database database7
Creating database database8
Creating database database9
3. Import the data from the holland backup into the DBaaS instance:
Command:
for database in $(grep -vw “mysql” /var/lib/mysqlbackup/default/newest/backup_data/MANIFEST.txt | awk ‘{print $1}’); do echo “Importing database $database” && zcat /var/lib/mysqlbackup/default/newest/backup_data/$database.sql.gz | mysql -h 67c87bb854cc70dde5bd6717a3a9b239f92aa1d1.rackspaceclouddb.com -u root -pspc9UqusADzHVy6mTmpDZMQeY36zQjNy3Cge $database; done
Output:
Importing database database1
Importing database database10
Importing database database2
Importing database database3
Importing database database4
Importing database database5
Importing database database6
Importing database database7
Importing database database8
Importing database database9
Quick check to make sure it worked. (It will complain if there are issues, it will typically just work without output other than the echo statement if its working)
Command:
mysql -h 67c87bb854cc70dde5bd6717a3a9b239f92aa1d1.rackspaceclouddb.com -u root -pspc9UqusADzHVy6mTmpDZMQeY36zQjNy3Cge -e “show databases”;
Output:
+———————+
| Database            |
+———————+
| information_schema  |
| database1           |
| database10          |
| database2           |
| database3           |
| database4           |
| database5           |
| database6           |
| database7           |
| database8           |
| database9           |
| #mysql50#lost+found |
| mysql               |
| performance_schema  |
+——————————+
4. Install Percona toolkit so we can use pt-show-grants to import users… This is the fun stuff!
Steps to install Percona Tool Kit:
CentOS:
yum install percona-toolkit-2.2.5-2.noarch
5. Use pt-show-grants to export the database information. 
Command:
pt-show-grants > /home/rack/mysql-grants.txt
Example of what the file looks like:
— Grants dumped by pt-show-grants
— Dumped from server Localhost via UNIX socket, MySQL 5.5.48-log at 2016-03-15 21:16:25
— Grants for ‘holland’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘holland’@’localhost’ IDENTIFIED BY PASSWORD ‘*E40DC2CA7F0D8DA8EC89B21A440FB0A7594BE915’;
— Grants for ‘root’@’127.0.0.1’
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’127.0.0.1’ IDENTIFIED BY PASSWORD ‘*E40DC2CA7F0D8DA8EC89B21A440FB0A7594BE915’ WITH GRANT OPTION;
— Grants for ‘root’@’::1′
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’::1′ IDENTIFIED BY PASSWORD ‘*E40DC2CA7F0D8DA8EC89B21A440FB0A7594BE915’ WITH GRANT OPTION;
— Grants for ‘root’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY PASSWORD ‘*E40DC2CA7F0D8DA8EC89B21A440FB0A7594BE915’ WITH GRANT OPTION;
GRANT PROXY ON ”@” TO ‘root’@’localhost’ WITH GRANT OPTION;
— Grants for ‘user1’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘user1’@’localhost’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
— Grants for ‘user10’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘user10’@’localhost’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
— Grants for ‘user2’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘user2’@’localhost’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
— Grants for ‘user3’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘user3’@’localhost’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
— Grants for ‘user4’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘user4’@’localhost’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
— Grants for ‘user5’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘user5’@’localhost’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
— Grants for ‘user6’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘user6’@’localhost’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
— Grants for ‘user7’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘user7’@’localhost’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
— Grants for ‘user8’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘user8’@’localhost’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
— Grants for ‘user9’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘user9’@’localhost’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
Here is the tricky part… I delete the “root”, Debian maintenance user,  and any other lines that we do not need… After that, you need to replace localhost with the IP or IP range of the web servers connecting. For this case, I’m doing 10.% as an example.  If you are not familiar with search and replace, here is an example when using vim
 :% s/localhost/10.%/g
Basically, this is what my sanitized file looks like without root users and 10.% grant statements. 
[root@carter-dbaas-import-test ~]# cat /home/rack/mysql-grants.txt
GRANT ALL PRIVILEGES ON *.* TO ‘holland’@’10.%’ IDENTIFIED BY PASSWORD ‘*E40DC2CA7F0D8DA8EC89B21A440FB0A7594BE915’;
GRANT ALL PRIVILEGES ON *.* TO ‘user1’@’10.%’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
GRANT ALL PRIVILEGES ON *.* TO ‘user10’@’10.%’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
GRANT ALL PRIVILEGES ON *.* TO ‘user2’@’10.%’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
GRANT ALL PRIVILEGES ON *.* TO ‘user3’@’10.%’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
GRANT ALL PRIVILEGES ON *.* TO ‘user4’@’10.%’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
GRANT ALL PRIVILEGES ON *.* TO ‘user5’@’10.%’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
GRANT ALL PRIVILEGES ON *.* TO ‘user6’@’10.%’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
GRANT ALL PRIVILEGES ON *.* TO ‘user7’@’10.%’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
GRANT ALL PRIVILEGES ON *.* TO ‘user8’@’10.%’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
GRANT ALL PRIVILEGES ON *.* TO ‘user9’@’10.%’ IDENTIFIED BY PASSWORD ‘*318361DEB5D3FF7D516EE5A5CE00EC29367DDC53’;
6. Import user grants to the DBaaS instance:
Command:
cat /home/rack/mysql-grants.txt | mysql -h 67c87bb854cc70dde5bd6717a3a9b239f92aa1d1.rackspaceclouddb.com -u root –pspc9UqusADzHVy6mTmpDZMQeY36zQjNy3Cge
Output:
No output if done correctly.  You can use the following command to check if done correctly. 
# mysql -h 67c87bb854cc70dde5bd6717a3a9b239f92aa1d1.rackspaceclouddb.com -u root -pspc9UqusADzHVy6mTmpDZMQeY36zQjNy3Cge -e “select user,host,password from mysql.user”;
+———-+———–+——————————————-+
| user     | host      | password                                  |
+———-+———–+——————————————-+
| root     | localhost | *2E96CD09DFE9B237F0C36A4A5B9FDACB8EBFC051 |
| carter   | %         | *C302861F744F8944E35C9FB54F06769E8EA0611E |
| raxmon   | 127.0.0.1 | *6A2C054466456F06988E2F2F868EC3A03D068CE0 |
| os_admin | localhost | *526E13B739AC2A906E92C7E7CFE6EE430E672F43 |
| root     | %         | *C86E05D52C0FA0A2B269A3F43AA90CD1828ECDD4 |
| holland  | 10.%      | *E40DC2CA7F0D8DA8EC89B21A440FB0A7594BE915 |
| user1    | 10.%      | *318361DEB5D3FF7D516EE5A5CE00EC29367DDC53 |
| user10   | 10.%      | *318361DEB5D3FF7D516EE5A5CE00EC29367DDC53 |
| user2    | 10.%      | *318361DEB5D3FF7D516EE5A5CE00EC29367DDC53 |
| user3    | 10.%      | *318361DEB5D3FF7D516EE5A5CE00EC29367DDC53 |
| user4    | 10.%      | *318361DEB5D3FF7D516EE5A5CE00EC29367DDC53 |
| user5    | 10.%      | *318361DEB5D3FF7D516EE5A5CE00EC29367DDC53 |
| user6    | 10.%      | *318361DEB5D3FF7D516EE5A5CE00EC29367DDC53 |
| user7    | 10.%      | *318361DEB5D3FF7D516EE5A5CE00EC29367DDC53 |
| user8    | 10.%      | *318361DEB5D3FF7D516EE5A5CE00EC29367DDC53 |
| user9    | 10.%      | *318361DEB5D3FF7D516EE5A5CE00EC29367DDC53 |
+———-+———–+—————————————————————+
Be sure to delete the root user at this time…. Were done. You can also just re-run the enable root user and it will simply reset the root password to a new random string. If you need to enable the root user again, feel free to do so and it will get a new root password so the one recorded in history shouldn’t matter as it will not be valid again.

jim has written 83 articles

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>