MySQL: Duplicate MySQL database

One of the client are requesting to duplicate their database to development database in case their production database have change mistakenly.

Scenario:
– MySQL Database server
– Root password: [email protected]
– Production Database: db1
– Development Database: db2
– Development User: db_user
– Development User Password: [email protected]

To do this, I choose to use mysqldump to done it.

1. First of all, you must create the development database named db2.

mysql -u root -p'[email protected]' -e "create database db2"

2. Next, you may duplicate the database data from db1 to db2.

mysqldump -u root -p'[email protected]' db1 | mysql -u root -p'[email protected]' db2

3. Now, you need to assign the MySQL user permission to the db2(development) database.
a) Log into the database server.

mysql -u root -p'[email protected]'

b) Grant the database privilege,

mysql> GRANT ALL PRIVILEGES ON db2.* TO [email protected] ;

MySQL Stored Procedures Permission problem

With MySQL 5.0.x and if you are using Stored procedures, you might found out the normal user are not able to use it to call the Stored procedures.

Although this is fixed when you are using MySQL 5.1.x, but your server might still stick to the MySQL 5.0.x. To overcome this problem, you may follow the step as below to get it fixed.

Log into the MySQL server to run the SQL Query.
1. Type in, GRANT SELECT ON mysql.proc TO [email protected]’10.10.10.10’; where username is the MySQL username and 10.10.10.10 is your IP or MySQL hostname.
2. Second line, you may flush the privileges, type in,  FLUSH PRIVILEGES;

You have done.

Remote MySQL Access for cPanel

By right, MySQL is one of the engine that used up lots of I/O and Memory resource from a server. Somehow you may use this scenario to setup so-called Clustering Web and MySQL server to reduce the I/O usage.

To setup remote MySQL server, you need 3 main steps as below.
1. Grant remote access to cPanel root user.
2. Add remote host information to /root/.my.cnf file.
3. Configure PHPMyAdmin configuration.

1. Grant remote access to cPanel root user,
To grant remote access to cPanel root user, you will need to amend the MySQL privilege as line below.
mysql> GRANT ALL PRIVILEGES ON *.* TO [email protected]`192.168.1.1` IDENTIFIED BY ‘password’ WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO [email protected]`mysql.mickgenie.com` IDENTIFIED BY ‘password’ WITH GRANT OPTION;

2. Add remote host information to /root/.my.cnf file,
Next, you will need to change the host of the MySQL location to the slave server.
[client]
user=”root”
pass=”password”
host=”mysql.mickgenie.com”

3. Configure PHPMyAdmin information,
Open file /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php, configure the setting as below.
$i++;
$cfg[‘Servers’][$i][‘host’] = ‘mysql.mickgenie.com’;
$cfg[‘Servers’][$i][‘port’] = ‘3306’;
$cfg[‘Servers’][$i][‘socket’] = ”;
$cfg[‘Servers’][$i][‘connect_type’] = ‘tcp’;
$cfg[‘Servers’][$i][‘extension’] = ‘mysql’;
$cfg[‘Servers’][$i][‘compress’] = FALSE;

You have done the setting.

Reset MySQL 5.1 Root password

Sometimes you might want to reset your used password since it is used for long time.

To reset MySQL database password, you may need to consider different Operating System and it’s version will have minor differences during the root password reset.

Today, Mick Genie will guide you how to reset MySQL database v5.1  through Windows Operating System and Linux Operating System.

For Windows Operating System,
1. Firstly, create a text file and name it as resetpassword.txt and you may save it to C drive.
2. Enter the following word to your text file.

UPDATE mysql.user SET Password=PASSWORD('[email protected]') WHERE User='root';
FLUSH PRIVILEGES;

Continue reading Reset MySQL 5.1 Root password