| Posted by takserver, 05-31-2015, 11:09 AM | | I need to move all databases from mysql 5.1 machine to new mysql 5.6 machine.
What should i do for move all databases no new machine?
NOTE: i have more than 500 databases.
|
| Posted by selinux, 05-31-2015, 11:13 AM | | Do a mysqldump to make a copy of your databases on the local machine then SCP/RSYNC them over to the new machine restore them.
|
| Posted by takserver, 05-31-2015, 11:44 AM | | Thank you very much for your answer.
How can i generate full databases backup with mysqldump?
I have more than 500 databases.
|
| Posted by HostingBig, 05-31-2015, 12:56 PM | | mysqldump -u root --all-databases > file.sql
|
| Posted by selinux, 05-31-2015, 01:33 PM | | The problem on doing an all-databases is that you are also backing up the mysql database on which could cause problems on the new database Engines, password etc.... Which is why doing an individual database migration be safer.
NOTE to OP make a backup to both database servers first before doing the --all-database backups you might break the new database.
Last edited by selinux; 05-31-2015 at 01:39 PM.
|
| Posted by HostingBig, 05-31-2015, 01:40 PM | | and since your going from 5.1 to 5.6 as
selinux pointed out the difference could cause issues
|
| Posted by Syslint, 05-31-2015, 01:57 PM | | I suggest you to take backup of db one by one . Do you have any control panel.? You can use the following scripts
1) Place all your db names in a file called dbnames.txt
ex : cat > dbnames.txt
mydb_one
mydb_two
2 ) Now execute the following command ,
# for db in `cat dbnames.txt`; do mysqldump $db > $db.sql ; done
This will dump al your dbs
|
| Posted by selinux, 05-31-2015, 03:05 PM | | Step 2 you will need to make sure the my.cnf is able to connect to mysql without password. If it doesn't paste these lines on your my.cnf save restart mysql.
[client]
user=root
password=x.x.x
X.X.X being the root password for mysql.
|
| Posted by takserver, 06-01-2015, 04:24 AM | | Done! and file.sql (30 Gb) is on new server /root now.
How can i restore all databases on new server now?
|
| Posted by andrewn, 06-01-2015, 04:44 AM | | mysql -u root -p < file.sql
|
| Posted by net, 06-01-2015, 04:49 AM | | Since you are upgrading anyway, go for MariaDB 10.
|
| Posted by servermates, 06-01-2015, 06:28 AM | | Hello,
First take the backup of all the databases using the command
mysqldump -u root --all-databases > database.sql
Then restore the database.sql file on our new machine after install mysql.
You can use mysql command for restoring the same.
mysql -u root -p < database.sql
|
| Posted by takserver, 06-13-2015, 03:18 PM | | Thank you very much for your help!
I try to generate full backup by "mysqldump --all-databases > alldb.sql" as you tols me before.
But randomly i see some error like "mysqldump: Got error: 1146: Table 'abadgar2_3.j85ut_hadis' doesn't exist when using LOCK TABLES"
How can i generate full backup skipping errors?
|
| Posted by Bbnuse, 06-14-2015, 09:39 PM | | Hi,
First check if everything's ok on that certain database:
This checks and repairs the db (in case it's corrupted or a table is crashed).
mysqldump errors can be the result of different issues, sadly there's not a magic command to fix everything. Sometimes you need to fix errors one by one.
Good luck
|
| Posted by takserver, 06-15-2015, 07:53 AM | | Thank you for your reply.
I wanna generate full databases backup by mysqldump -u root --all-databases > file.sql
How can i lock databases to be read-only to pervert update.
Because i have more than 2000 databases and maybe during backup time something will change.
|
| Posted by peet, 06-15-2015, 12:36 PM | | Hi,
If your tables are innodb you can use the option --single-transaction, it wont lock the tables but you will get a consistent data. Else default mysqldump will lock the tables while taking the backup.
One important thing what I believe is you needs to skip the mysql database from source server since if you overwrite the existing mysql on 5.6 with a lower version it wont going to work.
Please have a look into mysql manual which mention about the changes related with mysql 5.6
All the best.
Regards,
Peet
|
| Posted by servermates, 06-23-2015, 05:21 AM | | Try the below steps:
old server: mysqldump -u root --all-databases > file.sql
new server: mysql -u root -p < file.sql
After restoration complete. restart mysql on new server and try to login and see all databases are there. for this on new server.
mysql -u root -p
mysql> show databases;
Hope this helps.
|
|
 Add to Favourites
 Print this Article
|
