MySQL小筆記

啟動、關閉、重開mysql
/etc/init.d/mysql start
/etc/init.d/mysql stop
/etc/init.d/mysql restart

設定root密碼

mysqladmin -u root password 'xxxxx'

使用mysql
mysql -u root -p

更改密碼
mysql> SET PASSWORD FOR 'user'@'host' = OLD_PASSWORD('password');

安裝phpmyadmin
apt-get install phpmyadmin
然後在apache的DocumentRoot下
ln -s /usr/share/phpmyadmin phpmyadmin
便可用 http://ip/phpmyadmin　進入


讓遠端電腦可使用本機的mysql
~$ sudo vim /etc/mysql/my.cnf
將bind-address       = 127.0.0.1 註解掉

如果要限定某個ip才能使用 port 3306 (mysql default),
可以使用iptables來設定filter, 如下:
 // accept all tcp on port 3306 from localhost
~$ iptables -I INPUT 1 -i lo -p tcp --dport mysql -j ACCEPT
// accept all udp on port 3306 from localhost
~$ iptables -I INPUT 2 -i lo -p udp --dport mysql -j ACCEPT
// accept tcp on port 3306 from allowed_ip
~$ iptables -I INPUT 3 -i eth0 -p tcp --dport mysql -s allowed_ip -j ACCEPT
// accept udp on port 3306 from allowed_ip
~$ iptables -I INPUT 4 -i eth0 -p udp --dport mysql -s allowed_ip -j ACCEPT

最後記得在phpmyadmin中，將供遠端電腦登錄的user的主機來源改成allowed_ip
reference: Can't connect to [local] MySQL server