GIT – The mysqldump  will only export the data and the table structure but it will not include a users grants and privileges.

The main function of the MySQL privilege system (which is stored in mysql.user table) is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECT, INSERT, UPDATE, and DELETE.

Sample Setup

+—–+
| db1 | ————————-> -+
+—–+ |
old mysql server |
(192.168.1.8) |
+—–+ | ///////////////////////////////
| db2 | ————————-> -+——> // Internet (ISP router //
+—–+ | // with port 80 forwarding) //
new mysql server | ///////////////////////////////
(192.168.1.10) |
+—–+ |
| www1| ————————-> -+
+—–+
Apache web server
(192.168.1.5)

You need to move db1 server database called blogdb and its users to db2 server.

Install MySQL On DB2

Use the apt-get or yum command to install mysql on DB2 server:
# yum install mysql-server mysql-client
# service mysql start
// set root for new installation
# mysqladmin -u root password NEWPASSWORD


Make sure OpenSSH server is also installed on DB2.

Get Current MySQL, Usernames, Hostname, And Database Names

Type the following command at shell prompt to list username and hostname list, enter:

mysql -u root -B -N -p -e "SELECT user, host FROM user" mysql

Sample outputs:

gocit	192.168.1.5
blog	192.168.1.7
root	localhost
root	db1.gocit.vn.in

The first column is mysql username and second one is network host names. Now, type the following command to get exact details about grants and password for each user from above list:

mysql -u root -p -B -N -e"SHOW GRANTS FOR 'userName'@hostName"
mysql -u root -p -B -N -e"SHOW GRANTS FOR 'gocit'@192.168.1.5"

Sample outputs:

GRANT USAGE ON *.* TO ‘gocit’@’192.168.1.5’ IDENTIFIED BY PASSWORD ‘somePasswordMd5’
GRANT ALL PRIVILEGES ON `blogdb`.* TO ‘gocit’@’192.168.1.5’

Where,

  • gocit – MySQL login username
  • 192.168.1.5 – Another server or workstation to access this mysql server
  • somePasswordMd5 – Password stored in mysql database which is not in a clear text format
  • blogdb – Your database name

Now, you’ve all info and you can move database and users to a new server called db2 as follows using the combination of OpenSSH ssh client and mysql clients as follows:

ssh user@db2 mysql -u root -p’password’ -e “create database IF NOT EXISTS blogdb;”
ssh user@db2 mysql -u root -p’password’ -e “GRANT USAGE ON *.*
\ TO ‘gocit’@’192.168.1.5’ IDENTIFIED BY PASSWORD ‘somePasswordMd5′;”
ssh user@db2 mysql -u root -p’password’ -e “GRANT ALL PRIVILEGES ON
\ `blogdb`.* TO ‘gocit’@’192.168.1.5′;”
mysqldump -u root -p’password’ -h ‘localhost’ blogdb |
\ ssh user@db2 mysql -u root -p’password’ blogdb

You can test it as follows from Apache web server:

$ mysql -u gocit -h 192.168.1.10 -p blogdb -e 'show tables;'

A Note About Web Applications

Finally, you need to make changes to your application to point out to new a database server called DB2. For example, change the following from:

 
        $DB_SERVER = "db1.gocit.vn.in";
        $DB_USER = "gocit";
        $DB_PASS = "your-password";
        $DB_NAME = "blogdb";

To:

 
        $DB_SERVER = "db2.gocit.vn.in";
        $DB_USER = "gocit";
        $DB_PASS = "your-password";
        $DB_NAME = "blogdb";

A Sample Shell Script To Migrate Database

#!/bin/bash
# This script is licensed under GNU GPL version 2.0 or above
# ————————————————————
# SETME First – local mysql user/pass
_lusr=”root”
_lpass=”MySQLPassword”
_lhost=”localhost”

# SETME First – remote mysql user/pass
_rusr=”root”
_rpass=”mySQLPassword”
_rhost=”localhost”

# SETME First – remote mysql ssh info
# Make sure ssh keys are set
_rsshusr=”gocit”
_rsshhost=”db2.gocit.vn.in”

# sql file to hold grants and db info locally
_tmp=”/tmp/output.mysql.$$.sql”

#### No editing below #####

# Input data
_db=”$1″
_user=”$2″

# Die if no input given
[ $# -eq 0 ] && { echo “Usage: $0 MySQLDatabaseName MySQLUserName”; exit 1; }

# Make sure you can connect to local db server
mysqladmin -u “$_lusr” -p”$_lpass” -h “$_lhost” ping &>/dev/null ||

{ echo “Error: Mysql server is not online or set correct values for _lusr, _lpass, and _lhost”; exit 2; }

# Make sure database exists
mysql -u “$_lusr” -p”$_lpass” -h “$_lhost” -N -B -e’show databases;’ |
grep -q “^${_db}$” || { echo “Error: Database $_db not found.”; exit 3; }

##### Step 1: Okay build .sql file with db and users, password info ####
echo “*** Getting info about $_db…”
echo “create database IF NOT EXISTS $_db; ” > “$_tmp”

# Build mysql query to grab all privs and user@host combo for given db_username
mysql -u “$_lusr” -p”$_lpass” -h “$_lhost” -B -N \
-e “SELECT DISTINCT CONCAT(‘SHOW GRANTS FOR ”’,user,”’@”’,host,”’;’) AS
query FROM user” mysql
| mysql -u “$_lusr” -p”$_lpass” -h “$_lhost” \
| grep “$_user” \
| sed ‘s/Grants for .*/#### &/’ >> “$_tmp”

##### Step 2: send .sql file to remote server ####
echo “*** Creating $_db on ${rsshhost}…”
scp “$_tmp” ${_rsshusr}@${_rsshhost}:/tmp/

#### Step 3: Create db and load users into remote db server ####
ssh ${_rsshusr}@${_rsshhost} mysql -u “$_rusr” -p”$_rpass” -h “$_rhost” < “$_tmp”

#### Step 4: Send mysql database and all data ####
echo “*** Exporting $_db from $HOSTNAME to ${_rsshhost}…”
mysqldump -u “$_lusr” -p”$_lpass” -h “$_lhost” “$_db” | ssh ${_rsshusr}@${_rsshhost}
\ mysql -u -u “$_rusr” -p”$_rpass” -h “$_rhost” “$_db”

rm -f “$_tmp”

How Do I Use This Script?

Download the above script and edit it to set the following as per your setup:

# SETME First - local mysql DB1 admin user/password
_lusr="root"
_lpass="MySQLPassword"
_lhost="localhost"
# SETME First - remote mysql DB2 admin user/password
_rusr="root"
_rpass="mySQLPassword"
_rhost="localhost"
# Remote SSH Server (DB2 SSH Server)
# Make sure ssh keys are set
_rsshusr="gocit"
_rsshhost="db2.gocit.vn.in"

In this example, migrate a database called wiki with wikiuser username:

$ ./script.sh wiki wikiuser

Thảo luận bài viết tại forum : http://forum.gocit.vn/threads/migrate-mysql-database.459/

Bài viết này nằm trong dự án  website : www.gocit.vn, forum hổ trợ forum.gocit.vn , xem thêm tại link http://www.gocit.vn/du-an-linux-toan-tap/
Print Friendly

Comments

comments

Bài viết liên quan