Tabela users jest ciekawym przykładem migracji danych.
Często stosowana w serwisach gromadzących dane o użytkownikach (sklepy internetowe, blogi,…).
Co jednak gdy tworzono ją w pospiechu nie do końca przewidując potrzebne naszego serwisu.
Pozostaje jak tylko przebudować tabele zachowując dane w odpowiedniej formie.
Obie tabele mieszczą się na różnych serwerach.
Na serwerze A jest stara, na B nowa (przebudowana).

Poniżej tabele users_old (serwer A):

CREATE TABLE `users_old` (
`id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`login` varchar(50) NOT NULL DEFAULT '',
`haslo` varchar(50) NOT NULL DEFAULT '',
`imie` varchar(60) NOT NULL DEFAULT '',
`wiek` varchar(60) NOT NULL DEFAULT '',
`miasto` varchar(60) NOT NULL DEFAULT '',
`mail` varchar(60) NOT NULL DEFAULT '',
`ip` varchar(15) NOT NULL DEFAULT '',
`data` date NOT NULL DEFAULT '0000-00-00',
`hobby` MEDIUMTEXT NOT NULL,
PRIMARY KEY  (`id`),
KEY `login` (`login`),
KEY `haslo` (`haslo`),
KEY `imie` (`imie`),
KEY `miasto` (`miasto`)
) ENGINE=InnoDB DEFAULT CHARACTER SET latin2;

Nowa tabela (serwer B):

CREATE TABLE `users_new` (
`id` mediumint(8) NOT NULL AUTO_INCREMENT,
`login` varchar(50) NOT NULL,
`pass` varchar(32) NOT NULL,  /* MD5 */
`email` varchar(50) DEFAULT NULL,
`deleted` TINYINT(1) DEFAULT FALSE,
`date_start`  date NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY  (`id`),
UNIQUE KEY  (`login`),
UNIQUE KEY  (`email`)
) ENGINE=InnoDB DEFAULT CHARACTER SET latin2;

Lista zmian które wprowadzamy:
- hasła będą przechowywane w postaci zaszyfrowanej,
- pola: imie, wiek, miasto, ip, hobby będą w innych tabelach o innych typach,
- pola login i mail mają być unikatowe (nie mogą się powtarzać),
- wprowadziliśmy pole deleted które będzie służyć do usuwania kont użytkowników.

Do wprowadzenia zmian użyjemy polecenia SELECT przekierowując wynik do pliku users.txt.

Składnia:

SELECT
id, login, md5(haslo) as pass, mail as email, 0 as deleted, data as date_start
INTO OUTFILE
'/tmp/users.txt'
FIELDS
TERMINATED BY 't'
ESCAPED BY ''
OPTIONALLY ENCLOSED BY '"'
LINES
TERMINATED BY 'n'
FROM
users_old;

md5(haslo) – ma za zadanie zapisać hasła w zaszyfrowanej postaci.

Przesyłamy plik users.txt miedzy serwerami.

scp -pr /tmp/users.txt  login@adres_serwera:/tmp/

Na serwerze B do tabeli users_new importujemy dane.

LOAD DATA INFILE '/tmp/users.txt'
REPLACE
INTO TABLE users_new
CHARACTER SET latin2
FIELDS
TERMINATED BY 't'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES
TERMINATED BY 'n';

Przy dużej ilości danych może to chwilę potrwać.

Jeżeli nie otrzymalismy komunikatu o błędzie sprawdzamy czy wszystko się zgadza.

SELECT * FROM users_old;

+----+-------+-------+-------+------+---------+-------------------+------------+---------+-----------+
| 1 | user1 | pass1 | Karol | 22 | Warszawa | adres@mailowy.com | 192.168.100.1 | 2006-05-01 | Tenis |
| 2 | user2 | pass2 | Piotr | 20 | Pakość  | adres2@mailowy.com | 192.168.100.2 | 2006-05-02 | ...   |
+----+-------+-------+-------+------+---------+-------------------+------------+---------+-----------+


SELECT * FROM users_new;

+--+--------+---------------------------------------+-------------------------+---+-------------+
|  1 | user1 | 57210b12affe06adff6e54a93b1465aa | adres@mailowy.com    |  0 | 2006-05-01 |
|  2 | user2 | 259640f97af2b4379dd540ff4016654c   | adres2@mailowy.com |  0 | 2006-05-02 |
+--+--------+---------------------------------------+-------------------------+---+-------------+

Widać że pola się poprawnie zaimportowały.

Jeszcze na koniec zawartość pliku users.txt

1 "user1"  "57210b12af5e06ad2e6e54a93b1465aa" "adres@mailowy.com"  0  "2006-05-01"
2 "user2"  "259640f97ac2b4379dd540ff4016654c" "adres2@mailowy.com"  0  "2006-05-02"

Migracja powiodła się.