I have pretty big database, like with 5 000 000 rows in total. It is located in a remote server. I want to copy this database to my personal computer, for testing purposes. However I don't know the best option for this operation. Dumping the database does not look user friendly. Can I use Navicat's data transfer function to do this? Could it end with error? I think this operation can last few hours. Is there any other ways?
Mysql – How to move a MySQL database from 5.1 to 5.6
MySQLmysql-5.1mysql-5.6
Related Solutions
Further to my comment this is, putting it as politely as possible, a moderately crazy approach to getting the job done.
- Regardless of how mighty the ORM is, you are inherently changing the behaviour of the system by changing the RDMS.
- That your ORM can talk to two different database systems is a different proposition to the same database (i.e. schema) on two platforms. You can certainly create a RDBMS agnostic application but you'd never release it having tested against only one, would you?
- To accommodate the change of RDBMS, you create a stack of unnecessary ETL work to re-create the database objects, manage the type changes and then finally shift the data around.
Are there potential problems with hosting the testing database on the same server as the production server, assuming that the load on the testing database will be relatively minor?
You could mitigate the inherent risk in mixing development and production with appropriate segregation of security rights but accidents happen, people make mistakes, the wrong button get's clicked. If the server is comfortably spec'd and you are 100% convinced your testing won't place an undue load on the server, you could consider installing a second SQL instance side-by-side with the production instance but make sure all interested parties accept the risks first.
Alternatives:
- VirtualBox or similar. Better isolation if you have to run this on your production server and you keep the database in SQL Server.
- If you can't buy, rent. Rackspace Cloud, Amazon EC2, Microsoft Azure.
Believe it or not, I once wrote a post about why you should not do that (How can I disable utf8mb4 entirely on MySQL 5.5?). However, in the spirit of my old post and the commentary in it from @ChristopherSchultz, I will go out on a limb and tell you how you can do it, then tell you why you should not.
I once wrote a post about the home position of any empty binary log:
120
in MySQL 5.6107
in MySQL 5.5106
in MySQL 5.198
in MySQL 4.1/5.0- MySQL master binlog corruption
Over the years in this forum, I learned from someone (I think it was either Aaron Brown or Morgan Tocker) that there is a universal position for all binary logs regardless of the MySQL Version: position 4.
I once put that in an answer (Mar 05, 2013
: MySQL Replication without stopping master). In Step 06 from my answer I wrote this:
CHANGE MASTER TO
MASTER_HOST='10.1.20.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;
I also used position 4 in these other posts
Apr 02, 2014
: How do I fix Replication event checksum verification failed while reading from network. Error_code: 1743Mar 05, 2014
: Risk of changing clocktime on MySQL server host
Rarely do I repeat this info in any other posts for a reason. Personally, I fear that binlog events might be represented differently from version to version in terms of the size (in bytes) of each event. Believe it or not, over the past two weeks I have been upgrading DB Servers from MySQL 5.5. to MySQL 5.6. Due to mixed mode binary logging, there have been rare events when replication breaks and you cannot reset it from binlog files and positions by standard replication techniques. I have had to hose binary logs on Master, copy data, and setup replication from scratch a few times (5 out 400 VMs, but it still happened 5 times). I am very sure that replicating from a new Master to an old Slave would cause many more problems along these lines.
Therefore, I can only say that you can do it theoretically and MySQL may not object, that is, until MySQL Replication encounters a binlog event that is in a format it does not recognize and cannot interpret.
GIVE IT A TRY AT YOUR OWN RISK !!!
UPDATE 2014-11-18 22:32 EST
Just for official reference, this example CHANGE MASTER TO command
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
appears in the MySQL 5.6 Documentation. It's also in the MySQL 4.1 Documentation.
Thus, position 4 has always been known (I have only known a couple of years). Notwithstanding, I trust MySQL Replication from old Master to new Slave (but not on a permanent basis). I do not trust MySQL Replication from new Master to old Slave.
UPDATE 2014-11-19 17:47 EST
Please don't go down the Circular Replication path as it just adds to the risk of lost binlog events due to different versions. You should always replicate one direction to a newer version. Then, just failover to the newer version.
Related Question
- Thesql upgrading from 5.1 –> 5.6 do I have to do thesqldump before upgrade
- Mysql – Replicating from MySQL 5.1 master to 5.6 slave failing because ‘INSERT … VALUES (NOW())’ results in ‘Error_code: 1062’
- Mysql – Upgrading the thesql server from 5.1 to 5.6
- Fast Backup and Restore of MySQL Database – Revert Recent Changes
- Mysql – How to roll back thesql 5.6 to sql 5.1
- Mysql – (Partially) clone a huge DB for DEV purposes
Best Answer
It's all here. What's wrong with
(with valid options for your particular setup)? Check here for a sample. I don't know for sure, but Navicat is, in all likelihood doing something similar in the background. This is possibly better.