Hướng dẫn chuyển storage engine của MySQL từ MyISAM sang InnoDB và ngược lại

I. Giới thiệu:
  - MyISAM và InnoDB là các storage engine (cơ chế lưu trữ) của MySQL. Mỗi cơ chế sẽ lưu dữ liệu trên ổ cứng theo mỗi cách khác nhau và có ưu nhược điểm riêng. Một số tính năng của từng loại cơ chế:

  a. MyISAM:
                 + Chỉ có thể đọc table đồng thời mà không thể ghi đồng thời
                 + Tự sữa chữa và phục hồi dữ liệu tốt sau khi hệ thống bị crash.
                 + Hỗ trợ tìm kiếm full-text index.
                 + Tăng tốc độ ghi nhờ không ghi dữ liệu vào ổ cứng ngay mà ghi vào buffer trên RAM trước, sau một khoảng thời gian mới ghi vào ổ cứng
                 + Hỗ trợ nén dữ liệu giúp tăng tốc độ đọc dữ liệu nhưng dữ liệu sau khi nén không thể cập nhật được.

  b. InnoDB:
                 + Có khả năng phục hồi, sửa chữa tốt
                 + Là engine phức tạp nhất trong các engine của MySQL
                 + Hỗ trợ MVCC (Multiversion Concurrency Control) do đó table có thể đọc và ghi đồng thời
                 + Sử dụng clustered index do đó hiệu năng tìm kiếm theo primakey rất cao.
                 + Lưu dữ liệu trên 1 file (thuật ngữ gọi là tablespace).
                 + Hỗ trợ transactions

 II. Cách chuyển MyISAM sang InnoDB và ngược lại
  - Sử dụng cú pháp MySQL sau để chuyển từng table MyISAM sang InnoDB: ALTER TABLE table_name ENGINE = MyISAM;
  - Sử dụng cú pháp MySQL sau để chuyển từng table InnoDB sang InnoDB: ALTER TABLE table_name ENGINE = InnoDB;

  - Hướng dẫn chuyển tất cả table của một database từ MyISAM sang InnoDB:
     + Tạo file script: "vi script"
     + Thêm nội dung sau vào file script

         #!/bin/sh
         DBNAME="your-database"
         DBUSER="your-username"
         DBPWD="your-password"for t in $(mysql -u$DBUSER -p$DBPWD --batch --column-names=false-e "show tables" $DBNAME);do
         echo "Converting table $t"
         mysql -u$DBUSER -p$DBPWD -e "alter table $t engine=InnoDB" $DBNAME;done

         Trong đó "your-database" là database cần chuyển, "your-username" là user name của được gán quyền cho database, "your-password" là password của user.

    + Thêm quyền thực thi cho file script: "chmod +x script
    + Chạy file script: ./script

  - Khách hàng có thể sử dụng script trên để chuyển InnoDB sang MyISAM với script trên bằng cách sửa dòng "mysql -u$DBUSER -p$DBPWD -e "alter table $t engine=InnoDB" $DBNAME;done" thành "mysql -u$DBUSER -p$DBPWD -e "alter table $t engine=MyISAM" $DBNAME;done"

Read More

Cấu hình remote database trên vps/máy chủ riêng

Bước 1: Ssh vào server.

Bươc 2: Chỉnh sửa file my.cnf.

Đối với Centos khách hàng chỉnh sửa file my.cnf tại /etc/my.cnf . Tại [mysqld] thêm vào dòng bind-address=YOUR-SERVER-IP sau đó save file và dùng lệnh /etc/init.d/mysql restart để khởi động lại dịch vụ mysql.

Bước 3: Mở port 3306.

Để mở port 3306 khách hàng dùng lệnh iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT, tiến hành kiểm tra port bằng cách dùng telnet ví dụ như : telnet your-ip-server 3306


Bước 4: Phân quyền remote database

Đối với việc phần quyền remote database cho database mới khách hàng có chạy những lệnh sau đây
- Chạy lệnh mysql -uroot -p để đăng nhập vào mysql với quyền root
- Chạy lệnh CREATE DATABASE vinahost; để tạo database có tên vinahost.
- Chạy lệnh GRANT ALL ON vinahost.* TO test@'202.54.10.20' IDENTIFIED BY 'PASSWORD'; để phân quyền cho user test có host là 202.54.10.20 được toàn quyền thao tác trên database vinahost  ( Host chính là ip của client remote vào database, khách hàng có thể để host = % để cho tất cả ip từ client có thể remote vào database)

Đối với việc phân quyền remote cho database đã tồn tại.
- Chạy lệnh update db set Host='202.54.10.20' where Db='vinahost'; để cập nhật lài ip cho phần host là 202.54.10.20 cho database vinahost.
- Chạy lệnh update user set Host='202.54.10.20' where user='test';  để cập nhật lại phần host là 202.54.10.20 cho user test.

Bước 5: Remote vào database.

Tại server/vps remote gõ lệnh mysql -u test -h 66.66.66.55 -p

• -u test là username của mysql
• -h 66.66.66.55 là địa chỉ ip của server bị remote.
• -p là chỉ định mật khẩu của user sẽ nhập sau khi chạy lệnh trên

Read More

Xử lý lỗi font Tiếng Việt sau khi import CSDL

Để đảm bảo an toàn cho việc backup/restore databse, chuyển server mà ít bị lỗi font tiếng Việt trong MySQL, ta thực hiện các bước sau:

Bước 1. Export CSDL
Ta sử dụng công cụ phpMyAdmin để tiến hành export và import CSDL.
- Chọn Export > Custom - display all possible options.
- Ở phần character set of file chọn utf8, khi đó tất cả các table được export đều có character-set là utf8.
- Click Go để tiến hành export CSDL.

Bước 2. Import CSDLChọn tab Import, chọn file CSDL cần upload, chọn character set of the file là utf-8.
Sau đó nhấn Go để tiến hành import.

Read More

Hướng dẫn reset password root MySQL

1. Giới thiệu:
Bài viết này sẽ hướng dẫn cách thực hiện reset password root MySQL theo từng bước cụ thể.

2. Các bước thực hiện:

Bước 1: Stop dịch vụ MySQL

#/etc/init.d/mysqld stop

Bước 2: Start dịch vụ MySQL

# mysqld_safe --skip-grant-tables &

Bước 3: Kết nối tới MySQL

# mysql -u root

Bước 4: Tạo password root MySQL mới.

mysql> use mysql;

mysql> update user set password=PASSWORD("NEWPASSWORD") where User='root';

mysql> flush privileges;

mysql> quit

Bước 5: Stop MySQL Server.

# /etc/init.d/mysqld stop

Bước 6: Start MySQL Server và kiểm tra password mới.

# /etc/init.d/mysqld start

# mysql -u root -p

Read More

Các phương thức import cơ sở dữ liệu MySQL

1. Giới thiệu:
Trong trường hợp bạn muốn sử dụng cơ sở dữ liệu mới, bạn cần sử dụng chức năng "Tạo cơ sở dữ liệu mới" trong cPanel. Bài viết này hướng dẫn các bạn cách Import cơ sở dữ liệu MySQL.
2. Cách thực hiện Import:
a. Import cơ sở dữ liệu sử dụng phpMyAdmin: Phương thức này thích hợp khi bạn import cơ sở dữ liệu đơn giản, import từ các định dạng khác hoặc import một phần cơ sở dữ liệu.
Sau khi xác định cơ sở dữ liệu bạn muốn sử dụng, bạn kích hoạt hệ thống quản lý phpMyAdmin trong cPanel và làm theo các bước sau:
1. Chọn cơ sở dữ liệu bạn muốn dùng ở danh sách bên trái trong giao diện của phpMyAdmin.
2. phpMyAdmin sẽ hiển thị chi tiết cấu trúc của cơ sở dữ liệu đó, với các tab ở phía trên.
3. Chọn tab Import, phpMyAdmin sẽ hiển thị hộp thoại để upload file cơ sở dữ liệu. Bạn bấm vào nútBrowse và chọn file chứa dữ liệu cần import.
4. Sau khi chọn được file cần thiết, bấm nút Go (bạn nên để các cấu hình khác của giao diện import ở giá trị mặc định). phpMyAdmin sẽ cần thời gian để upload và import cơ sở dữ liệu mới cho bạn. Chú ý không đóng cửa sổ trình duyệt và chờ đến khi tác vụ hoàn tất.

b. Import cơ sở dữ liệu qua SSH: Phương thức này đặc biệt thuận tiện khi bạn cần import cơ sở dữ liệu với dung lượng lớn (thường gây ra lỗi timeout khi upload/import qua http).
Đầu tiên bạn cần upload cơ sở dữ liệu mới (để import) lên máy chủ. Bạn nên đăng nhập và upload qua FTP (sử dụng binary mode nếu cần thiết - xem hướng dẫn). Sau đó qua SSH bạn sử dụng dòng lệnh sau
mysql --protocol tcp -h 127.0.0.1 -u username -p database_name < /đường_dẫn_tới_file_dữ_liệu.sql
trong đó username là tên đăng nhập vào cPanel của bạn, database_name là tên cơ sở dữ liệu bạn muốn sử dụng. Bạn có thể xem thêm hướng dẫn sử dụng SSH tại đây.
- Import cơ sở dữ liệu qua hệ thống sao lưu & phục hồi: Đăng nhập vào cPanel và sử dụng chức năng Quản lý sao lưu & phục hồi trong phần Quản lý trang web tại danh mục bên trái. Phương thức này thuận tiện để import các cơ sở dữ liệu bạn đã sao lưu qua hệ thống sao lưu & phục hồi của cPanel.

Read More

Tổng hợp các lệnh thường dùng để quản trị MySQL

1. Vị trí các tập tin CSDL:

/var/lib/mysql

2. Quản lý các tài khoản user và database admin:

Lưu ý: Trước khi thực hiện các câu lệnh bên dưới bạn cần phải đăng nhập vào MySQL bằng tài khoản quản trị (root).

Hiển thị tất cả tài khoản user trong MySQL:

Mã:

mysql> SELECT * FROM mysql.user;

Xóa tài khoản rỗng:

Mã:

mysql> DELETE FROM mysql.user WHERE user = ' ';

Xóa tất cả tài khoản không phải là tài khoản root:

Mã:

mysql> DELETE FROM mysql.user WHERE NOT (host="localhost" AND user="root");

Thay đổi tên tài khoản root thành dạng khó đóan (vì mục đích bảo mật):

Mã:

mysql> UPDATE mysql.user SET user="mydbadmin" WHERE user="root";

Tạo tài khoản quản trị mới:

Mã:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Thêm một tài khoản mới vào một CSDL chỉ định với quyền chỉ định:

Mã:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass';

Thêm một tài khoản quản trị mới vào một CSDL chỉ định với quyền FULL:

Mã:

mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Thay đổi mật khẩu của một tài khoản:

Mã:

mysql> UPDATE mysql.user SET password=oldpass("newpass") WHERE User='username';

Xóa một tài khoản:

Mã:

mysql> DELETE FROM mysql.user WHERE user="username";

3. Làm việc với CSDL

Hiển thị tất cả các CSDL hiện có:

Mã:

mysql> SHOW DATABASES;

Tạo một CSDL:

Mã:

mysql> CREATE DATABASE mydatabase;

Sử dụng một CSDL cụ thể:

Mã:

mysql> USE mydatabase;

Xóa một CSDL:

Mã:

mysql> DROP DATABASE mydatabase;

Đổi tên một CSDL từ dòng lệnh (không cần đăng nhập vào MySQL), lưu ý không có khoảng trắng giữa -p và mypass:

Mã:

mysqldump -u dbauser -pmypass databasename > mybackup.sql mysql -u dbauser -pmypass newdatabasename < mybackup.sql

Hiển thị 200 câu truy vấn gần nhất đến CSDL với tên bảng là "queries" và cột "query_id":

Mã:

mysql> SELECT * FROM queries ORDER BY query_id DESC LIMIT 200;

4. Làm việc với bảng

Hiển thị tất cả các bảng trong CSDL đã chọn:

Mã:

mysql> SHOW TABLES;

Hiển thị toàn bộ dữ liệu trong bảng chỉ định:

Mã:

mysql> SELECT * FROM tablename;

Đổi tên bảng:

Mã:

mysql> RENAME TABLE first TO second; hoặc mysql> ALTER TABLE mytable rename as mynewtable

Xóa bảng:

Mã:

mysql> DROP TABLE mytable;

5. Làm việc với bảng và record

Hiển thị các cột trong bảng:

Mã:

mysql> DESC mytable; hoặc mysql> SHOW COLUMNS FROM mytable;

Cập nhật một record thuộc bảng được chọn trước:

Mã:

mysql> UPDATE mytable SET mycolumn="newinfo" WHERE mycolumn="oldinfo";

Chọn dữ liệu trong một bảng thuộc CSDL chọn trước:

Mã:

mysql> SELECT * FROM mytable WHERE mycolumn='mydata' ORDER BY mycolumn2;

Thêm dữ liệu vào cột thuộc CSDL chọn trước:

Mã:

mysql> INSERT INTO mytable VALUES('column1data','column2data','column3data','column4data','column5data','column6data','column7data','column8data','column9data');

Xóa dữ liệu của một record thuộc cột:

Mã:

mysql> DELETE FROM mytable WHERE mycolumn="mydata";

6. Sao lưu CSDL

Sao lưu tất cả CSDL (không nén) từ dòng lệnh (không đăng nhập vào MySQL):

Mã:

backup: mysqldump --all-databases > alldatabases.sql restore: mysql -u username -pmypass < alldatabases.sql (no space in between -p and mypass)

Sao lưu tất cả CSDL (nén) từ dòng lệnh (không đăng nhập vào MySQL):

Mã:

Với bzip2: mysqldump --all-databases | bzip2 -c > databasebackup.sql.bz2 (dùng bunzip2 để giải nén) Với gzip: mysqldump --all-databases | gzip > databasebackup.sql.gz (dùng gunzip để giải nén)

Mã backup tự động tất cả CSDL với gzip:

Mã:

#!/bin/sh date=`date -I` mysqldump --all-databases | gzip > /var/backup/dbbackup-$date.sql.gz

Sao lưu một CSDL chỉ định:

Mã:

mysqldump -u username -pmypass databasename > backupfile.sql

Chỉ sao lưu cấu trúc CSDL:

Mã:

mysqldump --no-data --databases databasename > structurebackup.sql

Sao lưu CSDL và các bảng chỉ định thuộc CSDL đó:

Mã:

mysqldump --add-drop-table -u username -pmypass databasename table_1 table_2 > databasebackup.sql

Read More