» mysql の記事

nano /etc/yum.repos.d/MariaDB.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.5/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

yum update && yum upgrade

nano /etc/my.cnf

Change
log_slow_queries = /var/log/mysql/mysql-slow.log
From
slow-query-log-file = /var/log/mysql/mysql-slow.log

systemctl start mariadb

mysql_upgrade -u root -p

mysql -V

reset slave;<====
stop slave;
change master to master_log_file=’mysql-bin.000045′,master_log_pos=274497196;
START SLAVE;
SHOW SLAVE STATUS \G;

~ 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));

 

« Previous Page | HOME |

Smiley face

January 2021
S M T W T F S
 12
3456789
10111213141516
17181920212223
24252627282930
31