Shulou(Shulou.com)06/01 Report--

Demand:

Two servers A (192.168.1.1) and server B (192.168.1.2)

The data directories are all: / usr/local/mysql/data

Now you want to migrate the chenfeng database from server A / usr/local/mysql/data to server B, only the chenfeng library is migrated, and the other databases remain unchanged.

The steps are as follows:

On server B:

Build the database:

Mysql > create database chenfeng

Query OK, 1 row affected (0.02 sec)

Create the same table as on server A:

Mysql > use chenfeng

Mysql > CREATE TABLE `accident` (

-> `id` int (11) NOT NULL AUTO_INCREMENT

-> `accidentContent` varchar (4000) DEFAULT NULL COMMENT 'full name of accident'

-> `accidentDate`datetime DEFAULT NULL COMMENT 'accident time'

-> `accidentType` int (2) DEFAULT NULL COMMENT 'accident type (1. Fire accident II. Explosion accident 3. Poisoning and asphyxiation accidents 4. Burn accident 5. Leakage accident 6. Other dangerous chemical accidents)'

-> `accidentLevel` int (11) DEFAULT NULL COMMENT 'accident level (1. General accident 2. Major accident 3. Major accident 4. Particularly serious accident)'

-> `companyId` int (11) DEFAULT NULL COMMENT 'involved enterprise id'

-> `accidentLocation` varchar (255) DEFAULT NULL COMMENT 'accident location (province, city, county)'

-> `accidentHarm` varchar (255) DEFAULT NULL COMMENT 'accident casualties (X dead, Y injured, Z missing)'

-> `accidentCause` varchar (255) DEFAULT NULL COMMENT 'cause of accident'

-> `lng` double (22Pol 7) DEFAULT NULL COMMENT 'longitude'

-> `lat` double (22507) DEFAULT NULL COMMENT 'Latitude'

-> PRIMARY KEY (`id`)

->) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8

Uninstall the chenfeng tablespace file:

Mysql > alter table accident discard tablespace

Query OK, 0 rows affected (0.15 sec)

Warning: this statement deletes the current .ibd file.

On server A:

Lock the table:

Mysql (mdba@localhost:chenfeng 06:57:27) > flush tables accident for export

Query OK, 0 rows affected (0.00 sec)

Note: this command keeps .ibd files in a consistent state

Copy the data file:

Copy all the files from the tablespace (ibd) of the A server table to the corresponding data directory of the B server, and we can copy it only if the file is in a consistent state:

[root@localhost data] # scp-r chenfeng root@192.168.1.2:/usr/local/mysql/data

Accident.frm 100% 21KB 24.1MB/s 00:00

Db.opt 100% 61 320.5KB/s 00:00

Accident.ibd 100% 128KB 87.6MB/s 00:00

On server B:

Modify the permissions of the copied file:

[root@nginx data] # cd / usr/local/mysql/data

[root@nginx data] # chown-R mysql:mysql chenfeng

Log in to the database and import the tablespace:

Mysql > alter table accident import tablespace

Query OK, 0 rows affected, 1 warning (0.47 sec)

Note: when importing tablespaces, the page size of the destination database should match that of the source database, and you need to pay attention to the consistency of the tablespace id on both sides, otherwise an error will be reported.

Verify the data:

Mysql > show tables

+-+

| | Tables_in_chenfeng |

+-+

| | accident |

+-+

1 row in set (0.00 sec)

Mysql > select * from accident

+- -+- - -+ -+

| | id | accidentContent | accidentDate | accidentType | accidentLevel | companyId | accidentLocation | accidentHarm | accidentCause | lng | lat |

+- -+- - -+ -+

| | 1 | pay attention to safety near the end of the Lunar New year. A car accident occurred in Tangyin East of Yancheng City, Zaozhuang City, Shandong Province | 2017-01-27 00:00:00 | 2 | 1 | 1 | 0 people were killed, 0 injured and 0 missing | according to Mr. Pan, he was passing by Yancheng District on the way to Taierzhuang. A car accident occurred in the east of Tangyin. Fortunately, the three people on board were all right, and two were sent to hospital for treatment. The vehicle involved in the accident was a black sedan. According to Mr. Pan, the cause of the accident was that the car overtook | 120.7725010 | 29.7305020 |

| 2 | pay attention to safety near the end of the New year. A car accident occurred in Tangyin East of Yancheng City, Zaozhuang City, Shandong Province | 2017-02-27 00:00:00 | 2 | 1 | Yicheng County, Zaozhuang City, Shandong Province | 0 people were killed, 0 injured, 0 missing | according to Mr. Pan, he was passing by Yancheng District on the way to Taierzhuang, and a car accident occurred in the east of Tangyin. Fortunately, the three people on board were all right, and two were sent to hospital for treatment. The vehicle involved in the accident was a black car. According to Mr. Pan, the cause of the accident was that the car overtook.

. .

. .

. .

At this point, the Innodb tablespace migration is complete.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.