Tag: Mysql Server

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.

phpMyAdmin Socket Error

If you are webmaster who using the web interface database management, you shall be familiar with phpMyAdmin.

Then you might get these error from your phpMyAdmin when you logged into the phpMyAdmin.

#2002 – The server is not responding (or the local MySQL server’s socket is not correctly configured)

To overcome this error, you could have 2 ways to solve the problem.

Method 1:

Check if your mysql.sock file missing from /tmp.

If it is really missing from /tmp, you may create a symbolic link from /var/lib/mysql/ to /tmp.

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

You might ask why must do this, you may check the file from /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php and you need to check for the setting of cfg[‘Server’][‘socket’],  and you will see the setting set to /tmp/mysql.sock.

Method 2:

Edit the following file.
/usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php

Find $cfg[‘Servers’][$i][‘host’], change the value from localhost to 127.0.0.1.

Change MySQL Collation

Sometimes, the MySQL database collation could make your web content become question mark(?) and you might not know what is happening and what wrong from your scripting.

To understand what is the server default collation, there are two ways to done it as below.

[email protected] [~]# mysqladmin variables | grep collation
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |

OR

Login to MySQL from server.

[email protected] [~]# mysql -uroot -p
mysql> SHOW VARIABLES LIKE ‘collation%’;
+———————-+—————–+
| Variable_name | Value |
+———————-+—————–+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+———————-+—————–+
3 rows in set (0.00 sec)

With the above command, you will notice that the collation set from your server is utf8_general_ci. Assume that you wish to change them to latin1_general_ci, simply edit your /etc/my.cnf (Linux) or C:\MySQL5\my.ini file record from

collation-server=utf8_general_ci

to

collation-server=latin1_general_ci

But please aware that you should not simply change the collation as it might affected some of your database data become question mark(?).

Then you shall restart the MySQL services.