» mysql の記事

~ mysql Slave Error ~

connie 2017.06.22 | mysql | | No Comments

my.cnf 設定如下,可以避免slave error

binlog_format = mixed

replicate-ignore-table = xxxDB.xxxtable

slave_skip_errors=1062
#1062=duplicate key error

 

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

mysql> set global sql_slave_skip_counter=2;
Query OK, 0 rows affected (0.00 sec)

START SLAVE;

SHOW SLAVE STATUS \G;

 

如果Slave DB出現以下情況。

Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

先記住Master_Log_File,Exec_Master_Log_Pos

stop slave;

CHANGE MASTER TO MASTER_HOST='{SERVERIP}', MASTER_PORT={PORT}, MASTER_USER='{USER}', MASTER_PASSWORD='{PASSWORD}' ,  master_log_file='mysql-bin.000002' , master_log_pos=326554769 ;
Query OK, 0 rows affected (0.07 sec)

START SLAVE;

SHOW SLAVE STATUS \G;

 

~ 變更group concat最大長度的限制 ~

connie 2017.03.26 | mariadb, mysql | | No Comments
預設1024 data lenght 

nano my.cnf

[mysqld]

group_concat_max_len=200000

 

Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.  

[master]
nano my.cnf
binlog_format = mixed
/etc/init.d/mysql restart

[slave]
nano my.cnf
binlog_format = mixed
/etc/init.d/mysql restart

show variables like "binlog_format";

 

mysqladmin -u root -p var | grep -E 'character|collation' | tr -s ' '
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
nano  /etc/my.cnf
[mysqld]
...
...
character-set-server=utf8
systemctl stop mariadb
systemctl start mariadb
mysqladmin -u root -p var | grep -E 'character|collation' | tr -s ' '
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |

 

~ Warning: mysqli_connect(): (HY000/2002) ~

connie 2013.01.18 | php | | No Comments

如果php connect mysql 時出現以下情況

Warning: mysqli_connect(): (HY000/2002): 
No such file or directory in *****.php on line 8 
Warning: mysqli_query() expects parameter 1 to be mysqli, 
boolean given in *****.php on line 9 
Connect failed

只要將connect DB 由localhost 改回127.0.0.1 就可以了

define('DATABASE_SERVER', '127.0.0.1');

 

~ Mysql 取出任意3天的record ~

connie 2013.01.07 | mysql | | No Comments
SELECT `accountname` FROM `tablename` 
where `log_date`>'2012-12-30 00:00:00' 
and `log_date`<'2013-01-03 23:59:59' 
group by `log_date`,`accountname` 
having COUNT(DISTINCT date(log_date));

 

~ 列出連續5天有紀錄 ~

connie 2013.01.06 | mysql | | No Comments

先要create function

DELIMITER //
CREATE FUNCTION bits_find_N1(bits BIGINT, trait BIGINT)
  RETURNS BOOL
  BEGIN
    WHILE bits <> 0 DO
      IF ((bits & trait) = trait) THEN
        RETURN TRUE;
      END IF;
      SET bits = bits >> 1;
    END WHILE;
    RETURN FALSE;
  END//
  DELIMITER ;

mysql statement

SELECT accountname AS bit FROM tablename
WHERE log_date BETWEEN '2012-12-30 00:00:00' AND '2013-01-03 23:59:59'
GROUP BY uid
HAVING bits_find_N1(BIT_OR(1 << datediff(log_date, '2012-12-30')),
b'11111') IS TRUE;

如果某column 數目要到一定數目

SELECT accountname AS bit, sum(cardamount) FROM tablename
WHERE log_date BETWEEN '2012-12-30 00:00:00' AND '2013-01-03 23:59:59'
GROUP BY uid
HAVING bits_find_N1(BIT_OR(1 &lt;&lt; datediff(log_date, '2012-12-30')),
b'11111') IS TRUE and sum(cardamount)&gt;=1200;

詳細可參考

http://www.mysqlops.com/2012/03/06/an_interesting_query.html

Mac OS X Server Apache 無法存取本機 MySQL Server 解決方式

由 phpinfo 中得知,是將 Mac 中的 PHP 是將 mysql.sock 放在 /var/mysql 下,

而 Mac 中的 MySQL 是放在 /tmp/mysql.sock。

所以要由本機的 Web Server 利用 PHP 連接本機的 MySQL 就會一直出現無法連線的錯誤。

利用底下的指令可以解決這個問題

sudo mkdir /var/mysql
sudo chown mysql /var/mysql
sudo ln -s /tmp/mysql.sock /var/mysql/
« Previous Page | HOME |