Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Migrate mysql users to another server

Tags:

mysql

permissions

migration

mysqldump

debian

I have created a mysqldump --all-databases and transferred all my databases to the new server. It didn't work as debian-sys-maintusers password didn't match. So I change the password of this user. After that I restart my server and got this error.

ERROR 1577 (HY000) at line 1: Cannot proceed because system tables used by Event Scheduler were found damaged at server start ERROR 1547 (HY000) at line 1: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted

I dont know how many more errors will come after this. So I thought create dump with only databases which are associated with my applications (mysqldump --databases).

Now how do migrate the users? Is there any standard way?

More Information:

New Server version: 5.1.63-0+squeeze1 (Debian)
Old Server version: 5.0.51a-24+lenny5 (Debian)
like image 665
Shiplu Mokaddim Avatar asked Nov 04 '12 09:11

Shiplu Mokaddim

People also ask

Can I copy MySQL database to another server?

In cases where you need to transfer databases between different architectures, you can use mysqldump to create a file containing SQL statements. You can then transfer the file to the other machine and feed it as input to the mysql client. Use mysqldump --help to see what options are available.

What is migration in MySQL?

With the MySQL Workbench Migration Wizard, users can convert an existing database to MySQL in minutes rather than hours or days that the same migration would require using traditional, manual methods. The Migration Wizard allows you to easily and quickly migrate databases from various RDBMS products to MySQL.

1 Answers

You probably need to run mysql_upgrade, since your MySql versions are different.

As a general rule, however, do not copy the mysql system schema from one server to another. As a consequence, and as far as I know, there is no "standard" way of copying users and user privileges from one server to another.

If you really want/need to do it, try the following:

$> mysql --silent --skip-column-names -e"show grants for user@host"

The above outputs GRANT statements that you can feed straight away into your target server to create the user and give the same authorisations.

However, if your target server is empty, you could just move the whole data folder from your old server to the new server, and then run the standard upgrade procedure from 5.0 to 5.1 on the new server.

like image 165
RandomSeed Avatar answered Oct 07 '22 21:10

RandomSeed
Sign in to Comment

Related questions

How to use SOCI C++ Database library?
PHP OOP: how to use a generic MySQL class in other classes
What is meant by 'query modification' as an approach to implementing views?
select on results of a select
Google Maps Save Polygon and points in MySQL using PHP
MySQL 5.1 Partitioning
Is using extra whitespace to make MySQL queries more readable inefficient to the point of not being worth it?
Best practice for 'remember me' functionality?
When to open the connection using node-mysql module?
How to fix the Overhead and Effective problem on InnoDB table?
Is there a way to simulate GROUP BY WITH CUBE in MySql?
Convert lat/lng pairs using GeomFromText('POINT(1 1)') and insert in another column
What is the return type of a "count" query against MySQL using Java JDBC?
MySQL event scheduler in XAMPP
PDO::query vs. PDOStatement::execute (PHP and MySQL)
JDBC batch query for high performance
MySQL making whitespace matter
How to limit the sql execution time
Select row with largest timestamp in each category
ExpressionEngine textarea increase limit

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!