檢查、修復mysql資料表

檢查資料庫內所有資料表
mysqlcheck -c db_name -u user_id -p

檢查資料庫內指定資料表
mysqlcheck -c db_name tablename -u user_id -p

修復資料庫內所有資料表
mysqlcheck -r db_name -u user_id -p

修復資料庫內指定資料表
mysqlcheck -r db_name tablename  -u user_id -p

MySQL(MariaDB)將Latin1改成UTF-8

1. #vim /etc/my.cnf

2.將下列複製貼到檔案內
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

3. #reboot

處理mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

想用PHP自己寫一個功能撈cdr的記錄，但連接Elastix2.5的資料庫時發生下面的錯誤

mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD(‘your_existing_password’). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file

上網查了一下這是mysql密碼編碼是舊版的關係
使用下列sql查出所有帳號密碼長度
select user, length(password) from mysql.user;
如果清單列出來長度41是新編碼方式，16是舊編碼方式

再來使用下列sql變更密碼格式
set old_passwords=FALSE;
set password for 'root'@'%' = password('XXXX');
flush privileges;

之後更新頁面應該可以了

AsteriskNow 13 安裝後後續設定

之前IPPBX都是用Elastix 2.5，但由於Elastix之後改和3CX合作改為不開源了，剛好最近有新案子要做會結合IPPBX，所以改裝AsteriskNOW來試試，安裝不難省過介紹，後續有些samba及連接資料庫的需求，所以記錄一下步驟

1.設定IP、getway、DNS
ifconfig eth0 192.168.0.55 netmask 255.255.255.0
vi /etc/sysconfig/network-scripts/ifcfg-eth0

DEVICE=eth0
BOOTPROTO=static
BROADCAST=192.168.0.255
IPADDR=192.168.0.55
NETMASK=255.255.255.0
GATEWAY=192.168.0.1
ONBOOT=yes

vi /etc/resolv.conf
search
nameserver 8.8.8.8 (first)
nameserver 8.8.4.4 (second)

/etc/init.d/network restart


2.安裝samba
yum install samba

vi /etc/samba/smb.conf
[www]
path = /var/www/html
comment = www
browseable = yes
writable = yes
security = user

pdbedit -a -u root

/etc/init.d/smb restart
/etc/init.d/nmb restart

3.設定mysql權限
可遠端連線mysql
1.edit my.cnf
vim /etc/my.cnf
bind-address = 0.0.0.0  (modify 127.0.0.1 to 0.0.0.0)

/etc/init.d/mysqld restart

2.setting iptables(若要更安全可設定只允許特定IP)
開放網段
iptables -A INPUT -p tcp -s 192.168.0.0/24 --dport 3306 -j ACCEPT
192.168.0.0/16=192.168.0.0-192.168.255.255
192.168.0.0/24=192.168.0.0-192.168.0.255
或開放特定IP
iptables -A INPUT -p tcp -s 192.168.0.5 --dport 3306 -j ACCEPT
其他封鎖
iptables -A INPUT -p tcp --dport 3306 -j DROP

/etc/init.d/iptables save
/etc/init.d/iptables restart

3.remote access mysql
開放外部IP登入
GRANT ALL PRIVILEGES ON *.* TO username@'192.168.0.%' IDENTIFIED BY 'password';
設定下面才能用phpMyAdmin登入
GRANT ALL PRIVILEGES ON *.* TO username@'127.0.0.1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO username@'localhost' IDENTIFIED BY 'password';

FLUSH PRIVILEGES; (使權限表生效)


4.安裝phpadmin
yum install phpadmin
或
下載php相對應的版本，直接將檔案放到/var/www/html

開啟遠端連線mysql port:3306

1.edit my.cnf
vim /etc/my.cnf
bind-address = 0.0.0.0  (modify 127.0.0.1 to 0.0.0.0)

/etc/init.d/mysqld restart

3.setting iptables
開放特定網域或IP
iptables -A INPUT -s 192.168.0.0/24 -m tcp -p tcp --dport 3306 -j ACCEPT
其他IP封鎖
iptables -A INPUT -p tcp --dport 3306 -j DROP

/etc/init.d/iptables save
/etc/init.d/iptables restart


2.connect mysql
GRANT ALL PRIVILEGES ON *.* TO username@'192.168.0.%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES; (使權限表生效)