Taking Database Backup

We are using AWS Ubuntu 20.04 LTS Server and Latest MySql . We are trying to migrate databse to new server using mysqldump .

We have tried to take the backup of existing databse but it thrown a error messege.

/usr/bin/mysqldump  -u   user  -p'password' databse | gzip > /var/data/redmine/backup/redmine_date +%Y-%m-%d.gz

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

This happen because , in never version of mysql (>=8.0) It requires elevated previlages to backup db.

So we elevated previlages.

mysql> GRANT ALL PRIVILEGES ON redmine.* TO 'redmine'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS for redmine@localhost;

+--------------------------------------------------------------+
| Grants for redmine@localhost |
+--------------------------------------------------------------+
| GRANT USAGE ON . TO redmine@localhost |
| GRANT ALL PRIVILEGES ON redmine.* TO redmine@localhost |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

same error Again!

Checked mysql version

~$ mysql --version
mysql Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

“we found solution on mysql developer website “

This change affects users of the mysqldump command, which accesses tablespace information in the FILES table, and thus now requires the PROCESS privilege as well. Users who do not need to dump tablespace information can work around this requirement by invoking mysqldump with the --no-tablespaces option. 

/usr/bin/mysqldump   --no-tablespaces   -u   user   -p'password'  databse | gzip > /var/data/redmine/backup/redmine_date +%Y-%m-%d.gz

output:

mysqldump: [Warning] Using a password on the command line interface can be insecure.

MySql Database backup is now created .

Restoring Database

First Of all make sure to create a database with same name to new location where you want to migrate the DB.

Then run following command:

mysql -u username -p database < PATH/mysqlbackup.sql

Note: To transfer mysqlbackup.sql from one server to another you can use scp utility .

scp username@ip:filepath/mysqlbackup.sql    path_to_new_location

where,

username : username from old server from where you want to migrate DB

ip: ip of old server

path_to_new_location : path from current new server where you want to store file

And thats it , Our Database is now completely migrated.

Note : If you are migrating mysql database for redmine system , make sure to run db:migrate after database migration.

$sudo RAILS_ENV=production bundle exec rake db:migrate