cacti 使用非標準 mysql port

cacti 設定檔 include/{config.php,global.php}
預設為
$database_type = 'mysql';
$database_default = 'cacti';
$database_hostname = 'localhost';
$database_username = 'cactiuser';
$database_password = 'cactiuser';
$database_port = '3306';
$database_retries = 5;
$database_ssl = false;
$database_ssl_key = '';
$database_ssl_cert = '';
$database_ssl_ca = '';
若要使用非標準port、則要改成
$database_type = 'mysqli';
$database_default = 'cacti';
$database_hostname = '127.0.0.1';
$database_username = 'cactiuser';
$database_password = 'cactiuser';
$database_port = '3307';
$database_retries = 5;
$database_ssl = false;
$database_ssl_key = '';
$database_ssl_cert = '';
$database_ssl_ca = '';
Reference:https://forums.cacti.net/viewtopic.php?f=2&t=49756

使用 mysql cli 連線時、增加 timeout 選項

最近用 shell script 去拉線上機器的 mysql 資訊，但是有的機器是會換 IP 的、導致指令就卡在那邊，後續的都不會跑
查了官方手冊才發現有這個參數可以用 --connect-timeout

實際使用方式為
mysql -u xxxxx -p xxxx --connect-timeout=3 -h xxx.xxx.xxx.xxx -P 3306

超過三秒就自動放棄、才不會等到天荒地老

reference:https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_connect-timeout

利用 dockerfile 建立自己的 image

因為用 export 或是 save 移到別台都會出現問題，又不方便每次都上 docker hub 拉，只好自己在每台上面建 image

1.為避免每次都要從外網拉檔案、先手動把mysql拉下來放到內網某台有提供http服務的機器上
2.進入容器內、把 entrypoint.sh 複製出來成 docker-entrypoint.sh
3.建立 Dockerfile
FROM ubuntu:trusty

RUN groupadd -r mysql && useradd -r -g mysql mysql

RUN apt-get update && \
apt-get install -y curl binutils

RUN gpg --keyserver pgp.mit.edu --recv-keys A4A9406876FCBD3C456770C88C718D3B5072E1F5

RUN locale-gen en_US.UTF-8 &&\
update-locale

ENV LANG en_US.UTF-8

ENV LANGUAGE en_US:en

ENV LC_ALL en_US.UTF-8

RUN curl -SL "http://192.168.1.1/mysql-5.1.73-linux-x86_64-glibc23.tar.gz" -o mysql.tar.gz && \
curl -SL "http://192.168.1.1/mysql-5.1.73-linux-x86_64-glibc23.tar.gz.asc" -o mysql.tar.gz.asc && \
gpg --verify mysql.tar.gz.asc && \
mkdir /usr/local/mysql && \
tar -xzf mysql.tar.gz -C /usr/local/mysql --strip-components=1 && \
rm mysql.tar.gz* && \
rm -rf /usr/local/mysql/mysql-test /usr/local/mysql/sql-bench && \
rm -rf /usr/local/mysql/bin/*-debug /usr/local/mysql/bin/*_embedded && \
find /usr/local/mysql -type f -name "*.a" -delete && \
{ find /usr/local/mysql -type f -executable -exec strip --strip-all '{}' + || true; } && \
apt-get purge -y --auto-remove binutils && \
rm -rf /var/lib/apt/lists/*

ENV PATH $PATH:/usr/local/mysql/bin:/usr/local/mysql/scripts

WORKDIR /usr/local/mysql

VOLUME /var/lib/mysql

COPY docker-entrypoint.sh /entrypoint.sh

ENTRYPOINT ["/entrypoint.sh"]

EXPOSE 3306

CMD ["mysqld", "--datadir=/var/lib/mysql", "--user=mysql"]

4.#docker build -t="mysql-5.1.73" .

使用 docker 跑 mysql-5.1.73

來源 https://hub.docker.com/r/vsamov/mysql-5.1.73

直接拉下來使用
#docker pull vsamov/mysql-5.1.73

列出本機所有 image
#docker image ls

新建並啟動 && 使用本來的設定檔和同步時區
#docker run --name mysql \
-p 3306:3306 \
-v /var/lib/mysql:/var/lib/mysql \
-v /etc/my.cnf:/etc/my.cnf \
-v /var/log/mysql_slow.log:/var/log/mysql_slow.log \
-e "TZ=Asia/Taipei" \
-d vsamov/mysql-5.1.73:latest

停止容器
#docker stop mysql

列出所有容器
#docker ps -a

移除容器
#docker rm mysql

mariadb 10.0 使用 grant 指令時出現 Access denied for user 'root'@'localhost' (using password: YES)

工作環境中有 mysql 和 mariadb 10 混用

今天用下面這條命令要新增使用者、mysql 沒有問題、mariadb 卻一直出現錯誤
mysql> grant ALL PRIVILEGES ON *.* TO 'user'@'192.168.1.%' IDENTIFIED BY 'password';

原本以為和之前文章 MariaDB 使用 root 登入後、新增使用者出現 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 錯誤訊息 一樣

但是照著做卻發現一切正常

後來認真 google 以後才發現這篇 解決mariadb grant ERROR 1045 (28000): Access denied for user

原來是語法不一樣，後來使用下面這個語法就沒問題了、也就是 "ALL PRIVILEGES" 和 "*.*" 不能一起用

mysql> grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER on *.* to 'user'@'192.168.1.%' IDENTIFIED BY 'password';

MariaDB 使用 root 登入後、新增使用者出現 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 錯誤訊息

使用 cli 模式或是 phpmyadmin 新增使用者、都出現 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) 錯誤訊息

MariaDB [(none)]> GRANT ALL PRIVILEGES ON . TO 'tt123'@'192.168.0.%' IDENTIFIED BY PASSWORD '*36F6ABC284EDCE8CBB4D071795AF023333785872';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

一開始照下面文章的方式處理、但是都無效
解決 MYSQL 登入時，ERROR 1045 (28000) using password: NO 的方法
myqsql 登入問題- iT 邦幫忙::一起幫忙解決難題，拯救IT 人的一天 - iThome

後來發現log裡面出現錯誤訊息，跑了分析以後出現
[root@tt0001 ~]# mysqlcheck -a -p mysql
Enter password:
mysql.column_stats Table is already up to date
mysql.columns_priv Table is already up to date
mysql.db Table is already up to date
mysql.event Table is already up to date
mysql.func Table is already up to date
mysql.gtid_slave_pos OK
mysql.help_category Table is already up to date
mysql.help_keyword Table is already up to date
mysql.help_relation Table is already up to date
mysql.help_topic Table is already up to date
mysql.host Table is already up to date
mysql.index_stats Table is already up to date
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist in engine
status : Operation failed
mysql.ndb_binlog_index Table is already up to date
mysql.plugin Table is already up to date
mysql.proc Table is already up to date
mysql.procs_priv Table is already up to date
mysql.proxies_priv Table is already up to date
mysql.roles_mapping Table is already up to date
mysql.servers Table is already up to date
mysql.table_stats Table is already up to date
mysql.tables_priv Table is already up to date
mysql.time_zone Table is already up to date
mysql.time_zone_leap_second Table is already up to date
mysql.time_zone_name Table is already up to date
mysql.time_zone_transition Table is already up to date
mysql.time_zone_transition_type Table is already up to date
mysql.user Table is already up to date

但是去 ls /var/lib/mysql/mysql 又有出現檔案
最後按照這篇操作 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
先 drop 後再手動把檔案移開，最後跑 mysql_upgrade 就好了

[root@tt0001 tools]# mysql_upgrade -p
Enter password:
MySQL upgrade detected
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist in engine
status : Operation failed
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK

Repairing tables
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist in engine
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist in engine
status : Operation failed
Upgrading from a version before MariaDB-10.1
Phase 2/7: Installing used storage engines
Checking for tables with unknown storage engine
Phase 3/7: Fixing views from mysql
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1932 (42S02) at line 586: Table 'mysql.innodb_index_stats' doesn't exist in engine
ERROR 1932 (42S02) at line 590: Table 'mysql.innodb_table_stats' doesn't exist in engine
ERROR 1932 (42S02) at line 593: Table 'mysql.innodb_table_stats' doesn't exist in engine
FATAL ERROR: Upgrade failed
[root@tw01 tools]# mysql_upgrade -p
Enter password:
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1813 (HY000) at line 64: Tablespace for table '`mysql`.`innodb_table_stats`' exists. Please DISCARD the tablespace before IMPORT.
ERROR 1813 (HY000) at line 68: Tablespace for table '`mysql`.`innodb_index_stats`' exists. Please DISCARD the tablespace before IMPORT.
ERROR 1146 (42S02) at line 586: Table 'mysql.innodb_index_stats' doesn't exist
ERROR 1146 (42S02) at line 590: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1146 (42S02) at line 593: Table 'mysql.innodb_table_stats' doesn't exist
FATAL ERROR: Upgrade failed
[root@tw01 tools]#
[root@tw01 tools]# mysql_upgrade -p
Enter password:
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

清除 mariadb slave 上 multi_master 設定

1.傳統單 master 模式改多 master 模式
MariaDB [(none)]> stop slave;
MariaDB [(none)]> show slave status;
MariaDB [(none)]> reset slave all;
MariaDB [(none)]> change master 'conn' to master_host='master ip', MASTER_PORT=3306, master_user='帳號', master_password='密碼', master_log_file='mysql-bin.000001', master_log_pos=12345;
MariaDB [(none)]> start slave 'conn';
MariaDB [(none)]> show all slaves status \G 或 show slave 'conn' status \G


2.跳過錯誤
MariaDB [(none)]> stop slave 'conn';
指定來源名稱
MariaDB [(none)]> set @@default_master_connection='conn';
MariaDB [(none)]> set global sql_slave_skip_counter=1;
MariaDB [(none)]> star slave 'conn';


3.清除多 master 設定
指定來源名稱
MariaDB [(none)]> set @@default_master_connection='conn';
MariaDB [(none)]> reset slave all;
MariaDB [(none)]> set @@default_master_connection='';

shell script 使用 mysql 命令小技巧

mysql $db -BNse "select host from hostwerer"

-B, --batch, 也就是以[Tab]字元做為輸出結果的欄間分隔字元.

-N, --skip-column-names, 不顯示列訊息.

-s, --silent, silent mode, 不輸出執行過程的說明文字.

-e, --execute=statement, 執行statement的命令, 然後離開.


mysql $db -e "select host from hostwerer"



mysql $db -se "select host from hostwerer"


mysql $db -Ne "select host from hostwerer"


mysql $db -Nse "select host from hostwerer"



reference:
MySQL -Bse 是什意思?
How can I suppress column header output for a single SQL statement?

用 shell script 批次刪除 mysql process

把帳號 dbread 的連結全部砍光光

#!/bin/bash

user=dbmanger
pass=abc123

mysql -u $user -p $pass -e "show processlist;" > process.txt

for i in `cat process.txt|grep 'dbread'|awk '{print $1}'`
do
mysql -u $user -p $pass -e "kill $i;"
done


Mysql 主從同步錯誤( 5.6 -> 5.1 )

●Master 為 Mysql 5.6 版
●Slave 為 Mysql 5.1 版

●Slave log 出現
[ERROR] Error reading packet from server: Slave can not handle replication events with the checksum that master is configured to log; the first event 'mysql-bin.000004' at 120, the last event read from './mysql-bin.000004' at 120, the last byte read from './mysql-bin.000004' at 120. ( server_errno=1236)

●先到 Master 下
mysql> set global binlog_checksum='NONE';

●然後在 my.cnf 加入下面這行、才不用每次重啟都要改
binlog-checksum = none

●另外可以下指令確認
mysql> show variables like '%checksum%';

●然後下指令確認最新的binlog檔和pos
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)


●接下來到 slave 下指令
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1',MASTER_USER='slave',MASTER_PASSWORD='a12345',MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=120;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


參考：Mysql 5.6 Master到5.1主從同步錯誤1

解決無法安裝 php-mysql 的問題

●yum install php-mysql
[root@SRV54 soft]# yum install php-mysql
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
* base: mirror01.idc.hinet.net
* extras: mirror01.idc.hinet.net
* updates: mirror01.idc.hinet.net
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package php-mysql.i686 0:5.3.3-46.el6_6 will be installed
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16) for package: php-mysql-5.3.3-46.el6
Package mysql-libs is obsoleted by mysql-community-libs, but obsoleting package does not provide for r
--> Processing Dependency: libmysqlclient.so.16 for package: php-mysql-5.3.3-46.el6_6.i686
Package mysql-libs is obsoleted by mysql-community-libs, but obsoleting package does not provide for r
--> Finished Dependency Resolution
Error: Package: php-mysql-5.3.3-46.el6_6.i686 (updates)
Requires: libmysqlclient.so.16
Available: mysql-libs-5.1.73-5.el6_6.i686 (base)
libmysqlclient.so.16
Error: Package: php-mysql-5.3.3-46.el6_6.i686 (updates)
Requires: libmysqlclient.so.16(libmysqlclient_16)
Available: mysql-libs-5.1.73-5.el6_6.i686 (base)
libmysqlclient.so.16(libmysqlclient_16)
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest

google 了好久、才在某篇文章找到解答

問題的根源主要在於舊的mysql-libs和Mysql 5.6 有衝突，要裝MySQL-shared-compat，官方手冊有記載

rpm -ivh MySQL-shared-5.6.26-1.el6.i686.rpm MySQL-shared-compat-5.6.26-1.el6.i686.rpm

參考：[已解決] 服務器無法安裝 mysql2+ 無法 db:migrate

CentOS 6.3 學習筆記 (20) - Mysql replication 設定

/etc/my.cnf

注意事項：主從資料庫起始狀況要一樣

CentOS 6.3 學習筆記 (4) - mysql php(編譯版) 安裝

mysql php(編譯版) 安裝