1. Các lệnh cơ bản trong MySQL

1.1. Kiểm tra version MySQL

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 8.3.0     |
+-----------+
1 ROW IN SET (0.00 sec)

1.2. Thao tác với Database

1.2.1. Hiển thị danh sách Databases

mysql> SHOW DATABASES;
+------------------------+
| DATABASE               |
+------------------------+
| information_schema     |
| mysql                  |
| performance_schema     |
| sys                    |
| test                   |
+------------------------+
8 ROWS IN SET (0.04 sec)

1.2.2. Hiển thị thông tin database đã tạo

mysql> SHOW CREATE DATABASE test;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| DATABASE | CREATE DATABASE                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1 ROW IN SET (0.00 sec)

1.2.3. Tạo database mới

mysql> CREATE DATABASE `thutycoi` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 ROW affected (0.08 sec)
 
mysql> SHOW DATABASES;
+------------------------+
| DATABASE               |
+------------------------+
| bank_crawler           |
| information_schema     |
| mysql                  |
| performance_schema     |
| sys                    |
| template.1site.vn      |
| test                   |
| test-template.1site.vn |
| thutycoi               |
+------------------------+
9 ROWS IN SET (0.03 sec)

1.2.4. Chọn Database cần sử dụng

mysql> USE thutycoi;
DATABASE changed

1.2.5. Xoá database đã tạo

mysql> DROP DATABASE thutycoi;
Query OK, 0 ROWS affected (0.12 sec)
 
mysql> SHOW DATABASES;
+------------------------+
| DATABASE               |
+------------------------+
| bank_crawler           |
| information_schema     |
| mysql                  |
| performance_schema     |
| sys                    |
| template.1site.vn      |
| test                   |
| test-template.1site.vn |
+------------------------+
8 ROWS IN SET (0.39 sec)

1.3. Thao tác với table

1.3.1. Hiển thị thông tin table

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| users          |
+----------------+
1 ROW IN SET (0.02 sec)
 
mysql> SHOW CREATE TABLE users\G;
*************************** 1. ROW ***************************
       TABLE: users
CREATE TABLE: CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci
1 ROW IN SET (0.01 sec)
 
mysql> DESC users;
+-------+--------------+------+-----+---------+----------------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | INT          | NO   | PRI | NULL    | AUTO_INCREMENT |
| name  | VARCHAR(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 ROWS IN SET (0.11 sec)

1.3.2. Tạo table mới

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci
1 ROW IN SET (0.01 sec)

1.3.3. Tạo table mới giống bảng đã có

mysql> CREATE TABLE users_001 LIKE users;
Query OK, 0 ROWS affected (0.12 sec)
 
mysql> INSERT INTO users_001 SELECT * FROM users;
Query OK, 3 ROWS affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

1.3.4. Tạo bảng tạm

mysql> CREATE TEMPORARY TABLE temp_users AS SELECT * FROM users WHERE id = 1;
Query OK, 1 ROW affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM temp_users;
+----+--------+
| id | name   |
+----+--------+
|  1 | TungNT |
+----+--------+
1 ROW IN SET (0.00 sec)

1.3.5. Đổi tên bảng

mysql> RENAME TABLE users_001 TO users_002;
Query OK, 0 ROWS affected (0.07 sec)

1.3.6. Xoá bảng đã có

mysql> DROP TABLE users_002;
Query OK, 0 ROWS affected (0.04 sec)

1.4. Update AUTO_INCREMENT

mysql> ALTER TABLE TABLE_NAME AUTO_INCREMENT = 1;

1.5. Một số lệnh khác

mysqld --verbose --help # https://dev.mysql.com/doc/refman/8.4/en/server-options.html

Kiểm tra thư mục dữ liệu

SHOW VARIABLES LIKE 'datadir';

2. Tạo tài khoản và phân quyền MySQL

2.1.1. Tạo tài khoản

mysql> CREATE USER 'tungzero'@'localhost' IDENTIFIED BY '***';
Query OK, 0 ROWS affected (0.02 sec)

2.1.2. Gán quyền truy cập database cho tài khoản

Cú pháp:

GRANT ALL PRIVILEGES ON `db_name`.* TO 'user_name'@'localhost';

Ví dụ:

mysql> GRANT ALL PRIVILEGES ON `test`.* TO `tungzero`@'localhost';
Query OK, 0 ROWS affected (0.03 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 ROWS affected (0.02 sec)

Chú ý: Không nên cho phép tài khoản sử dụng tất cả các quyền như trên, vì như thế họ sẽ có toàn quyền kiểm soát database trên server.

Nên giới hạn quyền như sau:

mysql> CREATE USER 'blue001'@'localhost' IDENTIFIED BY '***';
Query OK, 0 ROWS affected (0.10 sec)
 
mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD ON *.* TO 'blue001'@'localhost' WITH GRANT OPTION;
Query OK, 0 ROWS affected (0.11 sec)
 
mysql> FLUSH PRIVILEGES;
Query OK, 0 ROWS affected (0.02 sec)
 
mysql> SHOW GRANTS FOR `blue001`@'localhost';
+---------------------------------------------------------------------------------------------------------------------------------+
| Grants FOR blue001@localhost                                                                                                    |
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, REFERENCES, ALTER ON *.* TO `blue001`@`localhost` WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------+
1 ROW IN SET (0.00 sec)

2.1.3. Xem quyền truy cập database đã gán tài khoản

mysql> SHOW GRANTS FOR `tungzero`@'localhost';
+------------------------------------------------------------+
| Grants FOR tungzero@localhost                              |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tungzero`@`localhost`               |
| GRANT ALL PRIVILEGES ON `test`.* TO `tungzero`@`localhost` |
+------------------------------------------------------------+
2 ROWS IN SET (0.00 sec)
 
mysql> SHOW GRANTS;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants FOR root@localhost                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                           |
| GRANT ALLOW_NONEXISTENT_DEFINER,APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_ANY_DEFINER,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,TRANSACTION_GTID_TAG,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 ROWS IN SET (0.00 sec)
 
mysql> SHOW GRANTS FOR CURRENT_USER;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants FOR root@localhost                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                           |
| GRANT ALLOW_NONEXISTENT_DEFINER,APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_ANY_DEFINER,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,TRANSACTION_GTID_TAG,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 ROWS IN SET (0.00 sec)

4. Backup & Restore

4.1. Backup

mysqldump database_name > database_name.sql
mysqldump --databases database_one database_two > two_databases.sql
mysqldump --all-databases > all_databases.sql
mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql

4.2. Restore

mysql database_name < database_name.sql
mysql --one-database database_name < all_databases.sql

4.3. Copy table từ file .ibd

Giả sử có database test và table users như sau:

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
 
CREATE TABLE: CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

Vào thư mục data lưu trữ MySQL, ví dụ: /opt/homebrew/var/mysql

tungnt@MacBook-Pro-cua-Nguyen-2 mysql % cd /opt/homebrew/var/mysql
tungnt@MacBook-Pro-cua-Nguyen-2 mysql % ls -la
total 1277248
-rw-r-----    1 tungnt  admin     196608 Aug  6 20:30 #ib_16384_0.dblwr
-rw-r-----    1 tungnt  admin    8585216 Aug  6 20:29 #ib_16384_1.dblwr
drwxr-x---   34 tungnt  admin       1088 Aug  6 17:50 #innodb_redo
drwxr-x---   12 tungnt  admin        384 Aug  6 17:50 #innodb_temp
drwxr-xr-x   41 tungnt  admin       1312 Aug  6 18:08 .
drwxrwxr-x   15 tungnt  admin        480 Nov 15  2023 ..
-rw-r-----    1 tungnt  admin       1435 Jun  8  2021 MBP-cua-Nguyen.err
-rw-r-----    1 tungnt  admin          4 Jun  5  2021 MBP-cua-Nguyen.pid
-rw-r-----    1 tungnt  admin     135021 Aug  6 20:29 MacBook-Pro-cua-Nguyen-2.local.err
-rw-r-----    1 tungnt  admin          6 Aug  6 17:50 MacBook-Pro-cua-Nguyen-2.local.pid
-rw-r-----    1 tungnt  admin   11601861 Nov  5  2023 MacBook-Pro-cua-Nguyen.local.err
-rw-r-----    1 tungnt  admin         56 May 25  2021 auto.cnf
drwxr-x---   13 tungnt  admin        416 Jul 28  2022 bank_crawler
-rw-r-----    1 tungnt  admin       6250 Jul 26 17:15 binlog.000208
-rw-r-----    1 tungnt  admin        883 Aug  6 17:38 binlog.000209
-rw-r-----    1 tungnt  admin        158 Aug  6 17:47 binlog.000210
-rw-r-----    1 tungnt  admin        181 Aug  6 17:49 binlog.000211
-rw-r-----    1 tungnt  admin       1279 Aug  6 20:29 binlog.000212
-rw-r-----    1 tungnt  admin         80 Aug  6 17:50 binlog.index
-rw-------    1 tungnt  admin       1676 May 25  2021 ca-key.pem
-rw-r--r--    1 tungnt  admin       1112 May 25  2021 ca.pem
-rw-r--r--    1 tungnt  admin       1112 May 25  2021 client-cert.pem
-rw-------    1 tungnt  admin       1680 May 25  2021 client-key.pem
-rw-r-----    1 tungnt  admin       4433 Aug  6 17:49 ib_buffer_pool
-rw-r-----    1 tungnt  admin   79691776 Aug  6 20:29 ibdata1
-rw-r-----    1 tungnt  admin   12582912 Aug  6 17:50 ibtmp1
drwxr-x---    8 tungnt  admin        256 Jun 15 10:14 mysql
-rw-r-----    1 tungnt  admin   71303168 Aug  6 20:29 mysql.ibd
-rw-r-----    1 tungnt  admin          5 Jun 15 10:14 mysql_upgrade_info
drwxr-x---  114 tungnt  admin       3648 Jun 15 10:14 performance_schema
-rw-------    1 tungnt  admin       1676 May 25  2021 private_key.pem
-rw-r--r--    1 tungnt  admin        452 May 25  2021 public_key.pem
-rw-r--r--    1 tungnt  admin       1112 May 25  2021 server-cert.pem
-rw-------    1 tungnt  admin       1680 May 25  2021 server-key.pem
drwxr-x---    3 tungnt  admin         96 May 25  2021 sys
drwxr-x---  118 tungnt  admin       3776 May  7 15:08 template@002e1site@002evn
drwxr-x---    3 tungnt  admin         96 Aug  6 17:45 test
drwxr-x---   89 tungnt  admin       2848 Jul 19  2023 test@002dtemplate@002e1site@002evn
-rw-r-----    1 tungnt  admin  234881024 Aug  6 20:30 undo_001
-rw-r-----    1 tungnt  admin  234881024 Aug  6 20:30 undo_002

Trong thư mục data trên, có thể nhìn thấy các database:

+------------------------+
| Database               |
+------------------------+
| bank_crawler           |
| information_schema     |
| mysql                  |
| performance_schema     |
| sys                    |
| template.1site.vn      |
| test                   |
| test-template.1site.vn |
+------------------------+

Vào trong từng thư mục database, có thể nhìn thấy các file table:

tungnt@MacBook-Pro-cua-Nguyen-2 mysql % cd test
tungnt@MacBook-Pro-cua-Nguyen-2 test % ls -la
total 224
drwxr-x---   3 tungnt  admin      96 Aug  6 17:45 .
drwxr-xr-x  41 tungnt  admin    1312 Aug  6 18:08 ..
-rw-r-----   1 tungnt  admin  114688 Jul 25 18:04 users.ibd

Ví dụ để clone bảng test thành bảng test_new ta làm như sau:

% mysql -uroot -p
 
mysql> SHOW DATABASES;
+------------------------+
| DATABASE               |
+------------------------+
| bank_crawler           |
| information_schema     |
| mysql                  |
| performance_schema     |
| sys                    |
| template.1site.vn      |
| test                   |
| test-template.1site.vn |
+------------------------+
8 ROWS IN SET (0.03 sec)
 
mysql> CREATE DATABASE `test_new` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
    -> ;
Query OK, 1 ROW affected, 2 warnings (0.04 sec)
 
mysql> SHOW DATABASES;
+------------------------+
| DATABASE               |
+------------------------+
| bank_crawler           |
| information_schema     |
| mysql                  |
| performance_schema     |
| sys                    |
| template.1site.vn      |
| test                   |
| test-template.1site.vn |
| test_new               |
+------------------------+
9 ROWS IN SET (0.04 sec)
 
mysql> USE test_new;
DATABASE changed
mysql> CREATE TABLE `users` (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `name` VARCHAR(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
Query OK, 0 ROWS affected, 3 warnings (0.05 sec)
 
mysql> SHOW TABLES;
+--------------------+
| Tables_in_test_new |
+--------------------+
| users              |
+--------------------+
1 ROW IN SET (0.00 sec)
 
mysql> ALTER TABLE users DISCARD TABLESPACE;
Query OK, 0 ROWS affected (0.12 sec)

Sau khi DISCARD TABLESPACE, copy file table từ thư mục database test sang thư mục test_new:

tungnt@MacBook-Pro-cua-Nguyen-2 mysql % ls -la test_new
total 0
drwxr-x---   2 tungnt  admin    64 Aug  6 20:28 .
drwxr-xr-x  41 tungnt  admin  1312 Aug  6 18:08 ..
tungnt@MacBook-Pro-cua-Nguyen-2 mysql % 
tungnt@MacBook-Pro-cua-Nguyen-2 mysql % cp test/users.ibd test_new 
tungnt@MacBook-Pro-cua-Nguyen-2 mysql % ls -la test_new           
total 224
drwxr-x---   3 tungnt  admin      96 Aug  6 20:29 .
drwxr-xr-x  41 tungnt  admin    1312 Aug  6 18:08 ..
-rw-r-----   1 tungnt  admin  114688 Aug  6 20:29 users.ibd

Sau khi copy xong, IMPORT TABLESPACE như sau:

mysql> ALTER TABLE users IMPORT TABLESPACE;
Query OK, 0 ROWS affected, 1 warning (0.07 sec)
 
mysql> SELECT * FROM users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | TungNT001 |
|  2 | VienLD    |
|  3 | CuongPT   |
+----+-----------+
3 ROWS IN SET (0.01 sec)

Tham khảo: https://dba.stackexchange.com/questions/174/how-can-i-move-a-database-from-one-server-to-another

I recently moved a 30GB database with the following stragegy:

Old Server
- Stop mysql server
- Copy contents of datadir to another location on disk (~/mysqldata/*)
- Start mysql server again (downtime was 10-15 minutes)
- compress the data (tar -czvf mysqldata.tar.gz ~/mysqldata)
- copy the compressed file to new server
New Server
- install mysql (don't start)
- unzip compressed file (tar -xzvf mysqldata.tar.gz)
- move contents of mysqldata to the datadir
- Make sure your innodb_log_file_size is same on new server, or if it's not, don't copy the old log files (mysql will generate these)
- Start mysql

Table of Contents