» mariadb の記事

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

yum remove mariadb mariadb-server

rm -rf /var/lib/mysql 

rm /etc/my.cnf

Optional step: rm ~/.my.cnf

預設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 |