Archive for the ‘DataBase’ Category

Create Database user

Sunday, March 27th, 2022

CREATE USER ‘aya_srv’@ IDENTIFIED BY ‘eweR43rddf’;
GRANT ALL PRIVILEGES ON aya_tv.* TO ‘aya_srv’@;
CREATE USER ‘aya_srv’@ IDENTIFIED BY ‘eweR43rddf’;
GRANT ALL PRIVILEGES ON aya_tv.* TO ‘aya_srv’@;



Promote postgres slave to master

Wednesday, February 23rd, 2022

su – postgres/usr/pgsql-14/bin/pg_ctl promote -D /var/lib/pgsql/14/data/

reset the MySQL administrator’s password (Windows)

Tuesday, March 21st, 2017


To solve the problem and reconnect Plesk with the MySQL server, follow these steps:

  1. Stop the MySQL server (but not “Plesk SQL Server”) from running on port 3306 using the Plesk Services Monitor tray utility.
  2. Edit the MySQL configuration file, %plesk_dir%\\Databases\\MySQL\\Data\\my.ini , and add the line skip-grant-tables to the [mysqld]section.
  3. Start the MySQL server.
  4. Log into MySQL without the password:



5. Change the password for the MySQL administrator:


UPDATE mysql.user SET Password = password(‘newpassword’) WHERE User = ‘admin’;


6. Remove skip-grant-tables from the MySQL configuration file and restart the service.

password should be reset already and no more issues



Downgrade Mysql in cpanel server

Tuesday, March 31st, 2015

You know downgrades are not recommended, it’s always best to stick with a newer version and work around whatever issues you have but if you really have to downgrade then this way is pretty easy and always works 99% of the time to be honest.


As always make sure you take BACKUPS of your databases and mysql directory before you continue..

Edit /var/cpanel/cpanel.config and look for the mysql-version line and edit the number to whatever version you need i.e.



Save it and run the following command:

/scripts/mysqlup --force

After this is done – login to your WHM control panel and run easyeapache and rebuild based on your last profile to connect everything back together.

After that you should be good to go.




How To Set Up Master Slave Replication in MySQL

Wednesday, December 17th, 2014

About MySQL replication

MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data,a way to analyze it without using the main database, or simply as a means to scale out.

This tutorial will cover a very simple example of mysql replication—one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and and one of the slave.

This tutorial will use the following IP addresses: Master Database Slave Database


This article assumes that you have user with sudo privileges and have MySQL installed. If you do not have mysql, you can install it with this command:

### sudo apt-get install mysql-server mysql-client

Step One—Configure the Master Database

Open up the mysql configuration file on the master server.

### sudo nano /etc/mysql/my.cnf

Once inside that file, we need to make a few changes.

The first step is to find the section that looks like this, binding the server to the local host:


### bind-address  =

The next configuration change refers to the server-id, located in the [mysqld] section. You can choose any number for this spot (it may just be easier to start with 1), but the number must be unique and cannot match any other server-id in your replication group. I’m going to go ahead and call this one 1.

Make sure this line is uncommented.

### server-id        = 1

Move on to the log_bin line. This is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log. For this step we simply need to uncomment the line that refers to log_bin:

### log_bin      = /var/log/mysql/mysql-bin.log

Finally, we need to designate the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need.

### binlog_do_db       = newdatabase

After you make all of the changes, go ahead and save and exit out of the configuration file.

Refresh MySQL.

### sudo service mysql restart

The next steps will take place in the MySQL shell, itself.

Open up the MySQL shell.

### mysql -u root -p

We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

### GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

Follow up with:


The next part is a bit finicky. To accomplish the task you will need to open a new window or tab in addition to the one that you are already using a few steps down the line.

In your current tab switch to “newdatabase”.

### USE newdatabase;

Following that, lock the database to prevent any new changes:


Then type in:


You will see a table that should look something like this:

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000001 |      107 | newdatabase  |                  |
1 row in set (0.00 sec)

This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.

If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.

Proceeding the with the database still locked, export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).

### mysqldump -u root -p --opt newdatabase > newdatabase.sql

Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.

### EXIT;

Import the database that you previously exported from the master database.

### mysql -u root -p newdatabase < /path/to/newdatabase.sql

Now we need to configure the slave configuration in the same way as we did the master:

### sudo nano /etc/mysql/my.cnf

We have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique. Since it is set on the default (still 1), be sure to change it’s something different.

### server-id       = 2

Following that, make sure that your have the following three criteria appropriately filled out:

### relay-log         = /var/log/mysql/mysql-relay-bin.log
### log_bin               = /var/log/mysql/mysql-bin.log
### binlog_do_db            = newdatabase

You will need to add in the relay-log line: it is not there by default. Once you have made all of the necessary changes, save and exit out of the slave configuration file.

Restart MySQL once again:

### sudo service mysql restart

The next step is to enable the replication from within the MySQL shell.

Open up the the MySQL shell once again and type in the following details, replacing the values to match your information:

### CHANGE MASTER TO MASTER_HOST='',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;

This command accomplishes several things at the same time:

  1. It designates the current server as the slave of our master server.
  2. It provides the server the correct login credentials
  3. Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.

With that—you have configured a master and slave server.

Activate the slave server:


You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.


If there is an issue in connecting, you can try starting slave with a command to skip over it:


All done.
thank you.

How do I upgrade MySQL that comes installed with Plesk on Windows?

Friday, December 5th, 2014

The Parallels Plesk Control Panel installed on VPS / VDS Containers and Dedicated Servers on our network also installs a version of MySQL Database Server. However this bundled version of MySQL is not always the most current available, and you may wish to upgrade MySQL to a more current version. Please follow the steps below to do so…


The following is taken from section “b)” at: …

MySQL server database engine for client databases. It’s located in %plesk_dir%databases\MySQL and uses port 3306 for connections. It can be updated to any latest stable version of MySQL.

To update the client MySQL server one needs to follow these steps:

1. Download the latest stable version of MySQL

2. Stop MySQL Server service.

3. Rename or backup the folder %plesk_dir%databases\mysql.

4. Create the folder %plesk_dir%databases\mysql and extract the downloaded mysql version to this folder.

5. Delete the folder %plesk_dir%databases\mysql\data.

6. Copy the folder data from the original (backed up) mysql to %plesk_dir%databases\mysql.






Cleaning large eximstats mysql database : Cpanel/WHM – Mysql

Wednesday, May 30th, 2012

If you are using cpanel/WHM you can go to /var/lib/mysql and use “du -sch *” command to find the mysql disk usage of each database accounts.

If the size of your eximstats database is getting large, you will have to do this.

Login as root in to your server using ssh.

Login to mysql

#use eximstats

Then from with the query browser run

>delete from sends;
>delete from smtp;
>delete from failures;
>delete from defers;

Now use df to check the free disk space.

How to fix if MySQL cannot telnet from outside the server

Friday, June 17th, 2011

If you unable to telnet to the port 3306 which is for MySQL from outside the server, firstly you need to check the
firewall. You need to make sure the firewall already allow port 3306.
If the firewall already allow the port, another thing to do is to comment the line below in my.cnf file:
[mysqld] #bindaddress=
After that you need to restart the MySQL service for it’s to take effect by using the command below:
root@server [/var/lib/mysql]# service mysql restart
Shutting down MySQL. … SUCCESS!
Starting MySQL. SUCCESS!

Table ‘mysql.servers’ doesn’t exist

Friday, June 17th, 2011

If you see the error above when try to add or delete the database from your control panel, kindly follow the steps
below to fix it:
[root@server ]# mysql_fix_privilege_tables –user=admin
–password=<admin pass> –verbose
You will see the output as below:
This script updates all the mysql privilege tables to be usable by
the current version of MySQL
You can safely ignore all ‘Duplicate column’ and ‘Unknown column’ errors
because these just mean that your tables are already up to date.
This script is safe to run even if your tables are already up to date!
ERROR 1060 (42S21) at line 132: Duplicate column name ‘File_priv’
ERROR 1060 (42S21) at line 138: Duplicate column name ‘Grant_priv’
ERROR 1060 (42S21) at line 139: Duplicate column name ‘Grant_priv’
ERROR 1060 (42S21) at line 140: Duplicate column name ‘Grant_priv’
ERROR 1060 (42S21) at line 151: Duplicate column name ‘ssl_type’
ERROR 1061 (42000) at line 161: Duplicate key name ‘Grantor’
ERROR 1054 (42S22) at line 188: Unknown column ‘Type’ in ‘columns_priv’
ERROR 1060 (42S21) at line 210: Duplicate column name ‘type’
ERROR 1060 (42S21) at line 220: Duplicate column name ‘Show_db_priv’
ERROR 1060 (42S21) at line 237: Duplicate column name ‘max_questions’ ERROR
1060 (42S21) at line 247: Duplicate column name ‘Create_tmp_table_priv’
ERROR 1060 (42S21) at line 250: Duplicate column name ‘Create_tmp_table_priv’
ERROR 1060 (42S21) at line 375: Duplicate column name ‘Create_view_priv’
ERROR 1060 (42S21) at line 378: Duplicate column name ‘Create_view_priv’
ERROR 1060 (42S21) at line 387: Duplicate column name ‘Show_view_priv’
ERROR 1060 (42S21) at line 390: Duplicate column name ‘Show_view_priv’
ERROR 1060 (42S21) at line 410: Duplicate column name ‘Create_routine_priv’
ERROR 1060 (42S21) at line 413: Duplicate column name ‘Create_routine_priv’
ERROR 1060 (42S21) at line 422: Duplicate column name ‘Alter_routine_priv’
ERROR 1060 (42S21) at line 425: Duplicate column name ‘Alter_routine_priv’
ERROR 1060 (42S21) at line 431: Duplicate column name ‘Execute_priv’
ERROR 1060 (42S21) at line 434: Duplicate column name ‘Execute_priv’
ERROR 1054 (42S22) at line 603: Unknown column ‘Event_priv’ in ‘where clause’
ERROR 1060 (42S21) at line 619: Duplicate column name ‘sql_mode’
ERROR 1060 (42S21) at line 658: Duplicate column name ‘originator’
ERROR 1060 (42S21) at line 662: Duplicate column name ‘time_zone’
ERROR 1060 (42S21) at line 665: Duplicate column name ‘character_set_client’
ERROR 1060 (42S21) at line 671: Duplicate column name ‘collation_connection’
ERROR 1060 (42S21) at line 677: Duplicate column name ‘db_collation’
ERROR 1060 (42S21) at line 683: Duplicate column name ‘body_utf8’
ERROR 1054 (42S22) at line 693: Unknown column ‘Trigger_priv’ in ‘where clause’ done
Now you should able to add or delete the database.

Import an SQL file directly via the MySQL command-line

Tuesday, May 24th, 2011

If you have a very large database (totaling 10’s or 100’s of megabytes), then uploading via HTTP is not suitable. Instead, it will be quicker and more efficient to import the file directly into the database using the mysql command-line utility on the server.

However, to do this you will need SSH access to the server. If you do not have SSH access, please see our article:

To import an SQL file:

  • First, upload the file via FTP onto your account (into your private/ directory is best as no-one else can access this).
  • Using the SSH client of your choice, log onto the server. You will start at your home directory (/var/www/vhosts/, where is the domain the SSH account is associated with).
  • Run the following command line:
mysql -u username -p database < filename
  • where username is the username created for database. Finally, filename is the filename (and path, if required) to the file you just uploaded.
  • For example, if you created the database forum, with the username forum (and the password f0rum), and uploaded backup.sql to the private/ directory of your domain, you can run the following command:
mysql -u forum -p forum < private/backup.sql
Enter password:

If you see the error “Access denied for user 'username'@'localhost'“, check that you are using the correct username and password – this means that either one of them (or both of them) failed.

Depending on the size (and complexity) of the SQL file, this can take from a few seconds to a few minutes to complete. If you want to cancel command while it is running, press Ctrl & C at the same time.

Also, if you have a zipped or gzipped file, you will need to de-compress it before using it (the mysql command line utility will not understand the compressed file). To do this, run the following:

unzip path/to/


gunzip path/to/file.gz

If you still do not know how to do then just send email email
you can find my email in contact us
Thank You