» 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 |

 

~ 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

~ 非localhost connect mysql 方法 ~

connie 2012.12.14 | mysql | | No Comments

如果要外來ip connect 去mysql
要去更改my.cnf

ubuntu 位置為

/etc/mysql/my.cnf
將bind-address 指向要connect 去mysql 的ip

bind-address   = 127.0.0.111

PS: bind-address 不能多於一個,如果多於一個的情況就需要comment 左skip-external-locking 同bind-address,再用firewall block

blog_07

 

| HOME |