Sandvox mysql6/15/2023 Version 3: master/slave + mysqldump/file-copy In HA environment you should use the following trick: Install pigz - on modern Xeon or Opteron processors, especially when you have 2 or more CPUs, it is MUCH faster than gzip. If your database files are read only you may skip stopping the server.Rsync -avz dirs-or-files server2:$mysql-data-dir Version 1: data-files copy (MYISAM only) ssh server1 Perhaps this is a better way of doing it: This worked with mysql 5.1.61 on ubuntu 64 bit 11.10 and mysql 5.1.63 on osx lion (macbook pro). All users and databases should now be set up correctly. Start the mysql server for the first time (from the preferences pane under System Preferences->mysql). Sudo chmod -R g+rx /usr/local/mysql/data/ Sudo chgrp -R wheel /usr/local/mysql/data/ Sudo chown -R foouser /home/foouser/mysql_data_folderīefore you start take a copy of the mysql folder on the mac to make sure you don't mess up anything.Īfter copying the folder, do the following on the mac machine: sudo chown -R _mysql /usr/local/mysql/data/ To get access to get folder on the ubuntu machine I had to use sudo i.e.: sudo cp /var/lib/mysql /home/foouser/mysql_data_folder Make sure you have a clean mysql of mysql (installed the dmg downloaded from mysql ), that (VERY IMPORTANT) has never been run.Ĭopy the /var/lib/mysql/ folder contents from the ubuntu machine on top of /usr/local/mysql/data/ contents on the mac. To copy all of the databases without having to do any dumping or similar: I can confirm that DTest's method also works for copying between ubuntu and osx. Just remember to have server-id explcitly set in the master /etc/my.cnf and a different number for server-id in the slave /etc/my.cnf You can create a replication slave like this. ![]() Step 08) service mysql start on ServerA (optional) Step 07) scp ServerA:/etc/my.cnf ServerB:/etc/ Step 04) Repeat Step 03 until an rsync takes less than 1 minute ![]() Step 03) rsync -archive -verbose -stats -partial -progress -human-readable ServerA:/var/lib/mysql ServerB:/var/lib/mysql It also helps perform a mysql shutdown faster) If your database is all MyISAM, you can skip this step. Step 02) On ServerA, run SET GLOBAL innodb_max_dirty_pages_pct = 0 from mysql and about 10 minutes (This purges dirty pages from the InnoDB Buffer Pool. ![]() Step 01) install the same version of mysql on ServerB that ServerA has If you are more than willing to live with some slow DB performance, you can perform a series of rsyncs from the old server (ServerA) to the new server (ServerB) even while mysql is still running on ServerA. Since two questions were merged here (jcolebrand): EDIT You can reload that dumpfile to any MySQL server of your choose. No need to cncern yourself with InnoDB semantics if you just want one table. For sake of simplicity, clarity, and brevity, simply perform a mysqldump of the desired table using the -single-transaction parameters to have perfect point-in-time dump of the table. You need not wait 24 hours.īased on the aforementioned quote from the Certification book, there are many factors that govern how to backup a specific InnoDB table. Then kill the mysql session with the 24 hour lock. One second later, perform the copy in another ssh session. In one ssh session to hold table as read only and hold the lock for 24 hours. You could run the following: LOCK TABLES mydb.mytable READ Of course, you could shutdown mysql and copy the 3 table files. You can even to this from a Windows server to a Linux Server or a MacOS. Since these file have no further logical association attach to it, migrating a table from one DB server to another. These files are used interdependently to represent the table from a logical standpoint in mysql. /var/lib/mysql/mydb/mytable.MYI (.MYI file)./var/lib/mysql/mydb/mytable.MYD (.MYD file).If mydb.mytable uses the MyISAM storage engine, the table will physically be manifested as three separate files There are two major ways based on storage engine to move individual tables.įor the given example we will suppose the following: Both machines must use IEEE floating-point format or else the.Both machines must use two's-complement integer arithmetic. ![]() According to the MySQL 5.0 Certification Study Guide, Chapter 32 Section 32.3.4, Pages 456,457 describe the Conditions for Binary Portability which bring out the following:
0 Comments
Leave a Reply. |