Xuất dữ liệu bằng mysqldump

mysqldump là công cụ dòng lệnh của MySQL dùng để sao lưu cơ sở dữ liệu. Công cụ này tạo ra chuỗi lệnh SQL có thể lưu vào tệp, chứa đầy đủ các câu lệnh cần thiết để tái tạo cơ sở dữ liệu như CREATE DATABASE, CREATE TABLE, INSERT, v.v. Giúp thực hiện di chuyển hoặc khôi phục cơ sở dữ liệu nhanh chóng và nhẹ nhàng.

Trong bảo trì hệ thống, việc xuất dữ liệu thường xuyên được thực hiện và mysqldump là công cụ phổ biến nhất. Bài viết liệt kê các thao tác thường gặp và giải thích chi tiết toàn bộ tham số có thể sử dụng.

Cú pháp cơ bản

Khi không cung cấp tham số, đầu ra sẽ bao gồm: câu lệnh kiểm tra cơ sở dữ liệu, xóa bảng, tạo bảng, khóa bảng, vô hiệu hóa chỉ mục, chèn dữ liệu, kích hoạt lại chỉ mục, mở khóa bảng.

1. Xuất tất cả cơ sở dữ liệu

mysqldump -uadmin -ppassword --all-databases > /tmp/tat_ca.sql

2. Xuất dữ liệu từ hai cơ sở dữ liệu csdl1 và csdl2

mysqldump -uadmin -ppassword --databases csdl1 csdl2 > /tmp/csdl1_csdl2.sql

3. Xuất bảng bang1 và bang2 từ csdl1

Lưu ý: Chỉ hỗ trợ xuất bảng từ một cơ sở dữ liệu duy nhất. Đầu ra sẽ không chứa câu lệnh kiểm tra cơ sở dữ liệu, chỉ bao gồm xóa bảng, tạo bảng, chèn dữ liệu.

mysqldump -uadmin -ppassword --databases csdl1 --tables bang1 bang2 > /tmp/csdl1.sql

4. Xuất điều kiện: bản ghi có id=1 từ bảng bang1

Chỉ áp dụng cho một bảng duy nhất:

mysqldump -uadmin -ppassword --databases csdl1 --tables bang1 --where='id=1' > /tmp/bang1.sql

5. Tạo tệp binlog mới (-F)

Sử dụng tham số -F để yêu cầu tạo tệp binlog mới sau khi xuất dữ liệu:

mysqldump -uadmin -ppassword --databases csdl1 -F > /tmp/csdl1.sql

6. Xuất cấu trúc bảng mà không có dữ liệu (--no-data)

mysqldump -uadmin -ppassword --no-data --databases csdl1 > /tmp/csdl1.sql

7. Di chuyển dữ liệu giữa hai máy chủ

Di chuyển dữ liệu từ máy chủ h1 sang h2 (cần tồn tại cơ sở dữ liệu csdl2 trên h2):

mysqldump --host=h1 -uadmin -ppassword --databases csdl1 | mysql --host=h2 -uadmin -ppassword csdl2

Kích hoạt nén dữ liệu bằng tham số -C:

mysqldump --host=192.168.80.137 -uadmin -ppassword -C --databases test | mysql --host=192.168.80.133 -uadmin -ppassword test

8. Ghi thông tin binlog (--dump-slave)

Thực thi trên máy chủ slave, tương đương lệnh SHOW SLAVE STATUS. Khi thiết lập giá trị 1, sẽ xuất lệnh CHANGE MASTER; giá trị 2 thêm chú thích trước lệnh. Tùy chọn này sẽ khóa toàn bộ bảng trừ khi sử dụng --single-transaction.

mysqldump -uadmin -ppassword --dump-slave=1 --database csdl1 > /tmp/csdl1.sql

9. Ghi vị trí binlog (--master-data)

Tương tự --dump-slave nhưng ghi vị trí binlog của máy chủ hiện tại, tương đương SHOW MASTER STATUS.

10. Tùy chọn --opt

Kích hoạt đồng thời: --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys. Mặc định bật, dùng --skip-opt để tắt.

mysqldump -uadmin -p --host=localhost --all-databases --opt

11. Đảm bảo tính nhất quán (--single-transaction)

Gửi lệnh BEGIN trước khi xuất dữ liệu, không khóa bảng mà dựa vào phiên bản dữ liệu. Chỉ hỗ trợ InnoDB, xung đột với --lock-tables. Nên kết hợp với --quick khi xuất bảng lớn.

--quick, -q: Không đệm truy vấn, xuất trực tiếp ra stdout (mặc định bật).

12. Khóa bảng (--lock-tables, -l)

Khóa tất cả bảng trước khi xuất bằng READ LOCAL để cho phép chèn song song với MyISAM. Với InnoDB/BDB, nên dùng --single-transaction thay thế.

Lưu ý: Khi xuất nhiều cơ sở dữ liệu, --lock-tables

`13. Xuất hàm và thủ tục (--routines, -R)`

mysqldump -uadmin -p --host=localhost --all-databases --routines

`14. Xuất cấu trúc không có dữ liệu`

mysqldump --opt -d csdl1 -uadmin -p > csdl1.sql

`15. Xuất dữ liệu không có cấu trúc`

mysqldump -t csdl1 -uadmin -p > csdl1.sql

`16. Nhập dữ liệu`

source csdl.sql;

`Giải thích các tham số`

# Xuất toàn bộ cơ sở dữ liệu
mysqldump -uadmin -p --all-databases

# Xuất toàn bộ không gian bảng
mysqldump -uadmin -p --all-databases --all-tablespaces

# Bỏ qua không gian bảng
mysqldump -uadmin -p --all-databases --no-tablespaces

# Thêm DROP DATABASE
mysqldump -uadmin -p --all-databases --add-drop-database

# Thêm DROP TABLE (mặc định bật)
mysqldump -uadmin -p --all-databases

# Bỏ qua DROP TABLE
mysqldump -uadmin -p --all-databases --skip-add-drop-table

# Thêm khóa bảng (mặc định bật)
mysqldump -uadmin -p --all-databases

# Bỏ qua khóa bảng
mysqldump -uadmin -p --all-databases --skip-add-locks

# Cho phép tên cột trùng từ khóa
mysqldump -uadmin -p --all-databases --allow-keywords

# Thêm lệnh SLAVE
mysqldump -uadmin -p --all-databases --apply-slave-statements

# Chỉ định thư mục mã hóa
mysqldump -uadmin -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets

# Bỏ qua chú thích
mysqldump -uadmin -p --all-databases --skip-comments

# Tương thích hệ thống khác
mysqldump -uadmin -p --all-databases --compatible=ansi

# Xuất tối giản
mysqldump -uadmin -p --all-databases --compact

# Sử dụng INSERT đầy đủ
mysqldump -uadmin -p --all-databases --complete-insert

# Nén dữ liệu
mysqldump -uadmin -p --all-databases --compress

# Bao gồm tùy chọn bảng
mysqldump -uadmin -p --all-databases

# Xuất nhiều cơ sở dữ liệu
mysqldump -uadmin -p --databases test mysql

# Ghi log debug
mysqldump -uadmin -p --all-databases --debug

# Kiểm tra bộ nhớ
mysqldump -uadmin -p --all-databases --debug-check

# Thông tin debug
mysqldump -uadmin -p --all-databases --debug-info

# Thiết lập mã hóa mặc định
mysqldump -uadmin -p --all-databases --default-character-set=utf8

# Chèn chậm
mysqldump -uadmin -p --all-databases --delayed-insert

# Xóa log master
mysqldump -uadmin -p --all-databases --delete-master-logs

# Vô hiệu hóa chỉ mục
mysqldump -uadmin -p --all-databases 

# Ghi thông tin binlog
mysqldump -uadmin -p --all-databases --dump-slave=1

# Xuất sự kiện
mysqldump -uadmin -p --all-databases --events

# Sử dụng INSERT đa giá trị
mysqldump -uadmin -p --all-databases

# Bỏ qua INSERT đa giá trị
mysqldump -uadmin -p --all-databases--skip-extended-insert

# Định dạng tệp phân cách
mysqldump -uadmin -p test test --tab="/home/mysql" --fields-terminated-by="#" 

# Định dạng tệp bọc ký tự
mysqldump -uadmin -p test test --tab="/home/mysql" --fields-enclosed-by="#" 

# Định dạng tệp bỏ qua ký tự
mysqldump -uadmin -p mysql user --tab="/home/mysql" --fields-escaped-by="#" 

# Rửa log
mysqldump -uadmin -p --all-databases --flush-logs

# Cập nhật quyền
mysqldump -uadmin -p --all-databases --flush-privileges

# Bỏ qua lỗi SQL
mysqldump -uadmin -p --all-databases --force

# Hiển thị trợ giúp
mysqldump --help

# Xuất dữ liệu nhị phân
mysqldump -uadmin -p --all-databases --hex-blob

# Chỉ định máy chủ
mysqldump -uadmin -p --host=localhost --all-databases

# Bỏ qua bảng
mysqldump -uadmin -p --host=localhost --all-databases --ignore-table=mysql.user

# Ghi thông tin máy chủ
mysqldump -uadmin -p --host=localhost --all-databases --include-master-host-port

# Sử dụng INSERT IGNORE
mysqldump -uadmin -p --host=localhost --all-databases --insert-ignore

# Định dạng dòng
mysqldump -uadmin -p --host=localhost test test --tab="/tmp/mysql" --lines-terminated-by="##"

# Khóa toàn bộ bảng
mysqldump -uadmin -p --host=localhost --all-databases --lock-all-tables

# Khóa từng bảng
mysqldump -uadmin -p --host=localhost --all-databases --lock-tables

# Ghi lỗi vào tệp
mysqldump -uadmin -p --host=localhost --all-databases --log-error=/tmp/mysqldump_error_log.err

# Độ dài gói dữ liệu
mysqldump -uadmin -p --host=localhost --all-databases --max_allowed_packet=10240

# Kích thước bộ đệm mạng
mysqldump -uadmin -p --host=localhost --all-databases --net_buffer_length=1024

# Sử dụng giao dịch
mysqldump -uadmin -p --host=localhost --all-databases --no-autocommit

# Bỏ qua tạo cơ sở dữ liệu
mysqldump -uadmin -p --host=localhost --all-databases --no-create-db

# Bỏ qua tạo bảng
mysqldump -uadmin -p --host=localhost --all-databases --no-create-info

# Bỏ qua dữ liệu
mysqldump -uadmin -p --host=localhost --all-databases --no-data

# Bỏ qua thiết lập mã hóa
mysqldump -uadmin -p --host=localhost --all-databases --no-set-names

# Tùy chọn tối ưu
mysqldump -uadmin -p --host=localhost --all-databases --opt

# Sắp xếp theo khóa
mysqldump -uadmin -p --host=localhost --all-databases --order-by-primary

# Mật khẩu
--password, -p

# Kết nối ống dẫn (Windows)
mysqldump -uadmin -p --host=localhost --all-databases --pipe

# Cổng kết nối
--port, -P

# Giao thức kết nối
mysqldump -uadmin -p --host=localhost --all-databases --protocol=tcp

# Bỏ qua đệm
mysqldump -uadmin -p --host=localhost --all-databases 
mysqldump -uadmin -p --host=localhost --all-databases --skip-quick

# Bỏ qua tên bảng
mysqldump -uadmin -p --host=localhost --all-databases 
mysqldump -uadmin -p --host=localhost --all-databases --skip-quote-names

# Sử dụng REPLACE
mysqldump -uadmin -p --host=localhost --all-databases --replace

# Xuất trực tiếp vào tệp
mysqldump -uadmin -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt

# Xuất hàm/thủ tục
mysqldump -uadmin -p --host=localhost --all-databases --routines

# Thiết lập mã hóa
mysqldump -uadmin -p --host=localhost --all-databases 
mysqldump -uadmin -p --host=localhost --all-databases --skip-set-charset

# Giao dịch độc lập
mysqldump -uadmin -p --host=localhost --all-databases --single-transaction

# Ghi thời gian xuất
mysqldump -uadmin -p --host=localhost --all-databases
mysqldump -uadmin -p --host=localhost --all-databases --skip-dump-date

# Bỏ qua tùy chọn tối ưu
mysqldump -uadmin -p --host=localhost --all-databases --skip-opt

# Đường dẫn socket
mysqldump -uadmin -p --host=localhost --all-databases --socket=/tmp/mysqld.sock

# Xuất tệp phân cách
mysqldump -uadmin -p --host=localhost test test --tab="/home/mysql"

# Chỉ định bảng
mysqldump -uadmin -p --host=localhost --databases test --tables test

# Xuất trigger
mysqldump -uadmin -p --host=localhost --all-databases --triggers

# Thiết lập múi giờ
mysqldump -uadmin -p --host=localhost --all-databases --tz-utc

# Tên người dùng
--user, -u

# Thông tin chi tiết
--verbose, --v

# Phiên bản
--version, -V

# Điều kiện lọc
mysqldump -uadmin -p --host=localhost --all-databases --where="user='root'"

# Xuất XML
mysqldump -uadmin -p --host=localhost --all-databases --xml

# Thư mục plug-in
mysqldump -uadmin -p --host=localhost --all-databases --plugin_dir="/usr/local/lib/plugin"

# Quyền plug-in mặc định
mysqldump -uadmin -p --host=localhost --all-databases --default-auth="/usr/local/lib/plugin/<PLUGIN>"



    

        Thẻ:
            mysql
            sao lưu dữ liệu
            lệnh dòng lệnh
            innodb
            MyISAM
        
        Đăng vào ngày 1 tháng 7 lúc 18:52

Thành phố Cuồng loạn