Các lệnh MySQL thông dụng.

Các lệnh thông dụng : Giới thiệu với các bạn một số lệnh thông dụng chạy trên hệ điều hành .

Tác giả – Vien CNTT – DHQG Hanoi

Lệnh CREATE TABLE

– Cú pháp: CREATE TABLE Tên_bảng

(Tên_cột Loại_dữ_ liệu [Not Null]),

Primary Key( Tên khoá chính ),

Foreign Key( Tên khoá ngoài),…);

Trong đó:

Tên_ bảng: là xâu kí tự không chứa các ký tự trống và không trùng với cáctừ khoá

Tên _cột: là xâu kí tự bất kì không chứa kí tự trống, tên cột trong một bảng là duy nhất, thứ tự các cột không quan trọng

Loại_dữ_liệu: gồm một số loại dữ liệu sau:

Tiếp tục đọc

Bật tính năng truy cập từ xa của MySQL server

[Cấu hình] Bật tính năng truy cập từ xa của MySQL server

Mặc định tính năng truy cập từ xa bị cấm trên máy chủ cơ sở dữ liệu MySQL vì lý do bảo mật. Tuy nhiên, đôi khi bạn cần cung cấp tính năng truy cập từ xa tới máy chủ cơ sở dữ liệu từ nhà hoặc từ máy chủ web.

Truy cập MySQL từ xa

Bạn cần phải gõ các lệnh sau để bật tính năng truy cập từ xa.

Bước  # 1: Truy cập sử dụng SSH (nếu máy chủ bên ngoài trung tâm dữ liệu của bạn)

Đầu tiên, đăng nhập bằng SSH tới máy chủ MySQL:
ssh user@mysql.nixcraft.i

Bước # 2: Sửa tập tin my.cnf

Sau khi đã kết nối thành công bạn cần sửa tập tin cấu hình MySQL là  my.cnf bằng cách sử dụng một công cụ soạn thảo ví dụ vi.

  • Nếu bạn sử dụng Debian Linux thì tập tin cấu hình được lưu ở /etc/mysql/my.cnf
  • Nếu bạn sử dụng Red Hat Linux/Fedora/Centos Linux thì tập tin cấu hình được lưu ở /etc/my.cnf
  • Nếu bạn sử dụng FreeBSD thì tập tin cấu hình được lưu ở /var/db/mysql/my.cnf

Sửa /etc/my.cnf, bằng cách gõ lệnh:
# vi /etc/my.cnf

Bước # 3: Khi đã mở tập tin, tìm các dòng sau

[mysqld]
Chắc chắn là dòng skip-networking được đặt sau ký tự # hoặc bị xóa đi và thêm các dòng sau:
bind-address=YOUR-SERVER-IP
Ví dụ, nếu máy của MySQL của bạn có IP là 65.55.55.2 thì nội dung cấu hình sẽ đặt như sau:
[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/English
bind-address    = 65.55.55.2
# skip-networking
….
..
….
Trong đó,

  • bind-address : Địa chỉ IP để gán vào.
  • skip-networking : Không lăng nghe bất kỳ kết nối TCP/IP nào hếtl.Tất cả các tương tác với mysqld phải thông qua Unix sockets. Tùy chọn này được khuyến cáo sử dụng ở những hệ thống mà chỉ chấp nhận yêu cầu cục bộ. Khi bạn cần dùng tính năng truy cập từ xa thì phải bỏ dòng này trong tập tin my.cnf hoặc thêm ký tự # vào trước dòng.

Bước # 4 Lưu và đóng tập tin

Khởi động lại máy chủ MySQL bằng cách gõ:
# /etc/init.d/mysql restart

Bước # 5 Gán quyển truy cập cho địa chỉ IP

Kết nối tới máy chủ MySQL:
$ mysql -u root -p mysql

Gán truy cập tới một cơ sở dữ liệu mới

Nếu bạn muốn thêm một cơ sở dữ liệu mới là foo cho người dùng bar và địa chỉ IP là 202.54.10.20 thì bạn cần gõ lệnh sau:
mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@’202.54.10.20′ IDENTIFIED BY ‘PASSWORD’;

Làm sao để gán truy cập tới một cơ sở dữ liệu đã có sẵn?

Giả sử bạn luôn tạo kết nối từ IP 202.54.10.20 cho cơ sở dữ liệu webdb của người dùng webadmin, Để gán truy cập cho IP này, bạn gõ dòng lệnh sau:
mysql> update db set Host=’202.54.10.20′ where Db=’webdb’;
mysql> update user set Host=’202.54.10.20′ where user=’webadmin’;

Bước # 5: Thoát khỏi MySQL

Gõ lệnh thoát để thoát khỏi mysql: mysql> exit

Bước # 6: Mở cổng 3306

Bạn cần mở cổng 3306 bằng cách sử dụng iptables hoặc BSD pf firewall.

Ví dụ luật của iptables để mở iptables firewall

/sbin/iptables -A INPUT -i eth0 -p tcp –destination-port 3306 -j ACCEPT
hoặc chỉ cho phép truy cập từ xa từ máy chủ  web đặt ở 10.5.1.3:
/sbin/iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp –destination-port 3306 -j ACCEPT
hoặc chỉ cho phép truy cập từ xa từ subnet của mạng LAN của bạn 192.168.1.0/24:
/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp –destination-port 3306 -j ACCEPT
Cuối cùng lưu tất cả các luật:
# service iptables save

Ví dụ luật của FreeBSD / OpenBSD pf ( /etc/pf.conf)

pass in on $ext_if proto tcp from any to any port 3306
hoặc chỉ cho phép truy cập từ máy chủ web ở địa chỉ: 10.5.1.3:
pass in on $ext_if proto tcp from 10.5.1.3 to any port 3306  flags S/SA synproxy state

Bước # 7: Kiểm tra

Từ máy hệ thống từ xa hoặc trên máy của bạn gõ lệnh sau:
$ mysql -u webadmin –h 65.55.55.2 –p
Trong đó,

  • -u webadmin: webadmin là tên truy cập vào MySQL
  • -h IP or hostname: 65.55.55.2 là địa chỉ IP máy chủ MySQL hoặc hostname (FQDN)
  • -p : Hỏi mật khẩu

Bạn có thể sử dụng để kết nối tới cổng 3306 để kiểm tra:
$ telnet 65.55.55.2 3306

[Linux] Cấu hình replicate database MySQL Master-to-master.

altCó nhiều kiểu cấu hình replicate, tùy từng trường hợp cụ thể mà sử dụng theo cách nào cho phù hợp.
Ở đây trường hợp của tôi, bài tóan đặt ra như sau :
Code+database website được đặt trên 2 server (1 trong nước, 1 nước ngoài ). Yêu cầu 2 database này phải được đồng bộ thường xuyên với nhau.
Lí do tôi chọn cách cấu hình master to master là bởi thế, với cấu hình này thì 1 server vừa là master vừa là slave, tức nó vừa lắng nghe vừa ra hiệu cho server còn lại nếu có sự thay đổi dữ liệu trong database.

Cả 2 server sử dụng hệ điều hành CentOs, database MySQL version 5.x
Server 1 có IP dạng : 192.168.1.10
Server 2 có IP dạng : 192.168.1.11

Đăng nhập vào chế độ dòng lệnh của MySQL:
$ mysql -u root -p

Thực thi câu lệnh tương tự cho cả 2 server :

  1. GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’%’ IDENTIFIED BY ‘%slave_password%’;
  2. FLUSH PRIVILEGES;
  3. quit;

Trong đó replication là user,slave_password là mật khẩu của user replication.
Edit file my.cnf của server 1 :

  1. [mysqld]
  2. datadir=/var/lib/mysql
  3. socket=/var/lib/mysql/mysql.sock
  4. #user=mysql
  5. # Default to using old password format for compatibility with mysql 3.x
  6. # clients (those using the mysqlclient10 compatibility package).
  7. old_passwords=1
  8. max_connections = 400
  9. key_buffer = 16M
  10. myisam_sort_buffer_size = 32M
  11. join_buffer_size = 1M
  12. read_buffer_size = 1M
  13. sort_buffer_size = 2M
  14. table_cache = 1024
  15. thread_cache_size = 286
  16. interactive_timeout = 25
  17. wait_timeout = 1000
  18. connect_timeout = 60
  19. max_allowed_packet = 16M
  20. max_connect_errors = 10
  21. query_cache_limit = 1M
  22. query_cache_size = 16M
  23. query_cache_type = 1
  24. tmp_table_size = 16M
  25. skip-innodb
  26. log-bin=mysql-bin
  27. binlog-do-db=replicate_test
  28. binlog-ignore-db=mysql
  29. binlog-ignore-db=test
  30. server-id=1
  31. master-host = 192.168.2.11
  32. master-user = replication
  33. master-password = 111111
  34. master-port = 3306
  35. auto_increment_increment= 2
  36. auto_increment_offset   = 2
  37. slave-net-timeout = 30
  38. master-connect-retry = 30
  39. [mysql.server]
  40. user=mysql
  41. basedir=/var/lib
  42. [mysqld_safe]
  43. relay-log = relay-bin
  44. log-error=/var/log/mysqld.log
  45. pid-file=/var/run/mysqld/mysqld.pid
  46. max_allowed_packet = 16M
  47. [myisamchk]
  48. keybuffer = 32M
  49. sort_buffer = 32M
  50. read_buffer = 16M
  51. write_buffer = 16M

Edit file my.cnf của server 2 :

  1. [mysqld]
  2. datadir=/var/lib/mysql
  3. socket=/var/lib/mysql/mysql.sock
  4. #user=mysql
  5. # Default to using old password format for compatibility with mysql 3.x
  6. # clients (those using the mysqlclient10 compatibility package).
  7. old_passwords=1
  8. max_connections = 400
  9. key_buffer = 16M
  10. myisam_sort_buffer_size = 32M
  11. join_buffer_size = 1M
  12. read_buffer_size = 1M
  13. sort_buffer_size = 2M
  14. table_cache = 1024
  15. thread_cache_size = 286
  16. interactive_timeout = 25
  17. wait_timeout = 1000
  18. connect_timeout = 60
  19. max_allowed_packet = 16M
  20. max_connect_errors = 10
  21. query_cache_limit = 1M
  22. query_cache_size = 16M
  23. query_cache_type = 1
  24. tmp_table_size = 16M
  25. skip-innodb
  26. log-bin=mysql-bin
  27. binlog-do-db=replicate_test
  28. binlog-ignore-db=mysql
  29. binlog-ignore-db=test
  30. server-id=2
  31. master-host = 192.168.2.10
  32. master-user = replication
  33. master-password = 111111
  34. master-port = 3306
  35. auto_increment_increment= 2
  36. auto_increment_offset   = 2
  37. slave-net-timeout = 30
  38. master-connect-retry = 30
  39. [mysql.server]
  40. user=mysql
  41. basedir=/var/lib
  42. [mysqld_safe]
  43. relay-log = relay-bin
  44. log-error=/var/log/mysqld.log
  45. pid-file=/var/run/mysqld/mysqld.pid
  46. max_allowed_packet = 16M
  47. [myisamchk]
  48. keybuffer = 32M
  49. sort_buffer = 32M
  50. read_buffer = 16M
  51. write_buffer = 16M

Bạn cần để ý các thông số sau ở cả 2 server :

  1. log-bin=mysql-bin
  2. binlog-do-db=replicate_test
  3. binlog-ignore-db=mysql
  4. binlog-ignore-db=test
  5. server-id=2
  6. master-host = 192.168.2.10
  7. master-user = replication
  8. master-password = 111111
  9. master-port = 3306
  10. auto_increment_increment= 2
  11. auto_increment_offset   = 2

Chú thích các thông số :
binlog-do-db=replicate_test : chỉ định các bảng sẽ được thực hiện replicate
binlog-ignore-db=mysql : Chỉ định các bảng sẽ không được thực hiện replicate
server-id : Id chỉ định cho server.
master-host : Địa chỉ ip của host master
master-user,master-password : user và password theo master-host
master-port: cổng truy vấn của database.
auto_increment_offset : cần đặc biệt chú ý thông số này, trường hợp bạn không thiết lập thông số này database có trường autoincrement sẽ tăng theo giá trị +1 cho giá trị tiếp theo khi insert dữ liệu mới.
Để tránh trường hợp 2 database ở 2 server bị trùng giá trị autoincrement tôi đặt giá trị này auto_increment_offset=1 cho server1 và =2 cho server 2 với mục đích giá trị tự động tăng ở server1 là số lẻ , ở server 2 là số chẵn.

Đăng nhập chế độ dòng lệnh MySQL và thực hiện việc gán master cho server2:

  1. stop slave;
  2. CHANGE MASTER TO MASTER_HOST=’192.168.1.10′, MASTER_USER=’replication’, MASTER_PASSWORD=’%slave_password%’;
  3. start slave;

Thực hiện câu lệnh tương tự cho server 1 với MASTER_HOST là 192.168.1.11, user và password.

Kiểm tra sự hoạt động của cả 2 server :

  1. SHOW MASTER STATUS;
  1. SHOW SLAVE STATUS\G;

Nếu 2 option Slave_IO_Running and Slave_SQL_Running đều hiển thị Yes thì bạn đã thành công.
Nếu 1 trong 2 chưa thành công bạn cần kiểm tra lại user và password chính xác, hoặc master-port đã được mở hay chưa.

Viết bởi whatvn site:opensource.com.vn

[Linux] Cấu hình và tối ưu hoá cài đặt máy chủ cơ sở dữ liệu Mysql với MysqlTuner

MySQLTuner là một Script viết bằng Perl, giúp bạn cấu hình và đề xuất các giải pháp tăng hiệu quả hoạt động, độ ổn định máy chủ cơ sở dữ liệu dùng MySQL của bạn. Khi chạy MySQLTuner (MT), bạn sẽ nhanh chóng biết được các thông số về cài đặt MySQL và các số liệu quan trọng, cần thiết khác.

Vì sao ta nên sử dụng MySQLTuner? MT được viết đã để hỗ trợ bạn cấu hình, cải thiện tốc độ, tối ưu hoá những cài đặt quan trọng, giúp máy chủ cơ sở dữ liệu hoạt động tốt trên định mức có thể. MT không viết ra để đưa ra những lời khuyên, cũng như thông số sai lầm làm cho máy chủ của bạn hoạt động kém hiệu xuất hơn lúc ban đầu.

Một quản trị hệ thống cơ sở có kinh nghiệm luôn biết cách tốt nhất để tối ưu hoá hiệu xuất hoạt động của cơ sở dữ liệu là kiểm tra quá trình truy vấn từ máy chủ, MT được viết ra với một tập lệnh dành riêng cho quá trình này. Sau quá trình kiểm tra, bạn hoàn toàn có thể tự đưa ra đánh giá về hiệu xuất của máy chủ cơ sở dữ liệu của mình. MT ứng dụng những câu truy vấn và giám sát dựa trên các kĩ thuât đa dạng, nhằm tìm ra cách tối ưu nhất để giúp người quản trị hoàn thiện cấu hình cơ sở dữ liệu. Sau quá trình kiểm tra, ngoài việc chỉnh sửa lại cấu hình hệ thống cho phù hợp, có thể bạn cũng cần nghĩ đến các giải pháp nâng cấp phần cứng, nếu thực sự cần thiết.

Yêu cầu cấu hình:

MySqlTuner được viết bằng Perl, do đó máy chủ cần được cài đặt Perl để có thể chạy được MT, ứng dụng hoạt động tốt với MySQL 3.23 trở về sau. Hệ điều hành Linux/Unix, và quyền root hệ thống.

Cài đặt và sử dụng: Các script viết bằng perl có thể chạy mà không cần cài đặt, MT cũng vậy. Để bắt đầu sử dụng, bạn download MT ở http://mysqltuner.pl/

$ wget http://mysqltuner.pl

Cấp quyền thực thi cho mysqltuner.pl

$ su

Password: Gõ password của bạn

# chmod +x mysqltuner.pl

Công việc còn lại là chạy chương trình, quá trình kiểm tra bắt đầu, và đợi chương trình đưa ra những thông số thích hợp cho máy chủ cơ sở dữ liệu MySQL của bạn

# ./mysqltuner.pl

Tôi tin rằng bạn sẽ rất thích thú với công cụ nhỏ gọn này, vì đây là cách dễ dàng và trực quan nhất để các bạn học về cách cấu hình và tối ưu cấu hình MySQL.

Viết bởi whatvn site:opensource.com.vn

[Linux]Tạo bản sao lưu dự phòng cho MySQL với AutoMySQLBackup

altAutoMySQLBackup là một mã shell cho phép bạn sao lưu dự phòng cho cơ sở dữ liệu MySQL theo ngày, tuần, tháng sử dụng mysqldump. Nó có thể sao lưu nhiều cơ sở dữ liệu, nén bản sao lưu, sao lưu cơ sở dữ liệu remote, và gửi nhật kí tới email. AutoMySQLBackup sử dụng mysqldump để tạo bản lưu cho cơ sở dữ liệu của bạn. Lưu ý là mysqldump sẽ khóa cơ sở dữ liệu trong khi sao lưu đang làm việc và nó có thể mất vài giây cho tới vài phút tùy thuộc vào dung lượng của cơ sở dữ liệu. Nếu bạn chạy trên một website có lượng truy cập cao với cơ sở dữ liệu lớn thì AutoMySQLBackup là không phù hợp cho bạn!

Mã này không giúp bạn thậm chí cả khi ổ cứng bị hỏng. Bạn cần copy lại bản sao lưu một cách ngoại tuyến để có sự an toàn tốt nhất.

Sử dụng AutoMySQLBackup

Bạn có thể tải về AutoMySQLBackup như sau:

$ cd /usr/local/bin
$ wget http://mesh.dl.sourceforge.net/sourceforge/automysqlbackup/automysqlbackup.sh.2.5

Sau đó mở automysqlbackup.sh.2.5 và xem các tùy chọn cấu hình. Tất cả đều được chú thích. Bạn nên chỉnh sửa lại các thiết lập sau:

$ vi automysqlbackup.sh.2.5

[…]
USERNAME=root
[…]
PASSWORD=yourrootsqlpassword
[…]
DBHOST=localhost
[…]
# List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3″
DBNAMES=”db_ispconfig web1 web2 web3″
[…]
# Backup directory location e.g /backups
BACKUPDIR=”/backups”
[…]
# Email Address to send mail to? (user@domain.com)
MAILADDR=”user@domain.com”
[…]

DBNAMES có thể có 1 hoặc nhiều cơ sở dữ liệu, phân cách bằng khoảng trắng.

Nếu BACKUPDIR không tồn tại automysqlbackup.sh.2.5 sẽ tự động tạo nó.

Hãy chắc chắn bạn điền đúng mật khẩu và host cơ sở dữ liệu. Nếu bạn muốn sao lưu cơ sở dữ liệu local, hãy dùng localhost; nếu bạn muốn sao lưu cơ sở dữ liệu remote, hãy dùng the remote hostname (chú ý rằng cơ sở dữ liệu remote phải được bật ở phía máy đó!).

Bây giờ ta thực thi đoạn mã:

$ chmod 755 automysqlbackup.sh.2.5

Bây giờ bạn có thể chạy automysqlbackup.sh.2.5 như sau nếu bạn đang ở thư mục /usr/local/bin:

$ ./automysqlbackup.sh.2.5

… hoặc như sau nếu bạn đang ở thư mục khác:

$ automysqlbackup.sh.2.5

Đây là kết quả trả về:

server1:~# automysqlbackup.sh.2.5
======================================================================
AutoMySQLBackup VER 2.5

http://sourceforge.net/projects/automysqlbackup/

Backup of Database Server – server1.example.com
======================================================================
Backup Start Time Fri Oct 17 16:00:51 CEST 2008
======================================================================
Daily Backup of Database ( db_ispconfig )
Rotating last weeks Backup…

Backup Information for /backups/daily/db_ispconfig/db_ispconfig_2008-10-17_16h00m.Friday.sql
compressed uncompressed ratio uncompressed_name
37231 382465 90.3% /backups/daily/db_ispconfig/db_ispconfig_2008-10-17_16h00m.Friday.sql
———————————————————————-
Backup End Fri Oct 17 16:00:52 CEST 2008
======================================================================
Total disk space used for backup storage..
Size – Location
68K /backups

======================================================================
If you find AutoMySQLBackup valuable please make a donation at

http://sourceforge.net/project/project_donations.php?group_id=101066

======================================================================
server1:~#

Hãy xem thư mục /backups…

$ ls -l /backups

… và bạn sẽ thấy 3 thư mục con, daily, weekly, và monthly:

server1:~# ls -l /backups/
total 12
drwxr-xr-x 3 root root 4096 2008-10-17 16:00 daily
drwxr-xr-x 2 root root 4096 2008-10-17 16:00 monthly
drwxr-xr-x 3 root root 4096 2008-10-17 16:00 weekly
server1:~#

Các thư mục này sẽ chứa các thư mục con có tên sau khi cơ sở dữ liệu được bạn chọn để sao lưu. Ví dụ: nếu bạn chọn cơ sở dữ liệu db_ispconfig, sẽ có một thư mục /backups/daily/db_ispconfig chứa bản sao lưu:

$ cd /backups/daily/db_ispconfig
$ ls -l

server1:/backups/daily/db_ispconfig# ls -l
total 40
-rw-r–r– 1 root root 37231 2008-10-17 16:00 db_ispconfig_2008-10-17_16h00m.Friday.sql.gz
server1:/backups/daily/db_ispconfig#

Phần mở rộng .gz có nghĩa là nó được nén. Để khôi phục cơ sở dữ liệu, đầu tiên bạn cần xả nén cho nó:

$ gunzip db_ispconfig_2008-10-17_16h00m.Friday.sql.gz

Viết bởi whatvn site:opensource.com.vn