mysql配置文件

linux

my.cnf

[mysqld]
#server-id                      = 224
user = mysql
port                           = 3305
mysqlx_port                    = 33060
mysqlx_socket                  = /tmp/mysqlx.sock
datadir                        = /opt/mysql/data
socket                         = /tmp/mysql.sock
pid-file                       = /tmp/mysqld.pid
auto_increment_offset          = 2
auto_increment_increment       = 2 
log-error                      = /opt/mysql/log/error.log
slow-query-log                 = 1
slow-query-log-file            = /opt/mysql/log/slow.log
long_query_time                = 0.2
log-bin                        = bin.log
relay-log                      = relay.log
binlog_format                 =ROW
relay_log_recovery            = 1
character-set-client-handshake = FALSE
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
init_connect                   ='SET NAMES utf8mb4'
innodb_buffer_pool_size        = 1G
join_buffer_size               = 128M
sort_buffer_size               = 2M
read_rnd_buffer_size           = 2M
log_timestamps                 = SYSTEM
lower_case_table_names         = 1
default-authentication-plugin  =mysql_native_password

win

my.ini

[client]
port=3306

[mysql]
default-character-set=utf8mb4


[mysqld]
port=3306
#password=123456
#character-set-client-handshake=FALSE  
character-set-server=utf8mb4 
#collation-server = utf8mb4_general_ci  
init_connect='SET NAMES utf8mb4'


basedir="D:/360Downloads/mysql-5.7.20-winx64"
#Path to the database root
datadir="D:/360Downloads/mysql-5.7.20-winx64/data"

log-error="D:/360Downloads/mysql-5.7.20-winx64/log/mysqld_err.log"
#log-bin="D:/360Downloads/mysql-5.7.20-winx64/log/mysqld_bin.bin"

default-storage-engine=INNODB
#从 5.6开始，timestamp 的默认行为已经是 deprecated 了
explicit_defaults_for_timestamp=true

sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 

主从

doc https://dev.mysql.com/doc/refman/8.0/en/replication-howto-masterstatus.html

复制原理

• 主服务数据变更记录保存到binlog
• 从服务器的io线程请求主的binlog,写入到中继日志relaylog
• 从服务器的sql线程从中继日志读取事件，并在本地执行事件，从而实现数据的同步

新帐号

CREATE USER 'repl'@'192.168.16.232' IDENTIFIED BY '123456';

只复制

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.16.232';

主

配置mysql.cnf

[mysqld]
server-id=1

show master status;

File: mysql-bin.000001
Position: 765

从

配置mysql.cnf

[mysqld]
server-id=2
#设置为只读状态,针对普通MySQL数据库用户
read_only=1

set global read_only=1;–针对普通MySQL数据库用户设置为只读
set global super_read_only=1;–针对super类MySQL数据库用户设置为只读，比如root用户
//flush tables with read lock;–设定全局锁,如果只是需要只读，并不需要加锁
show global variables like “%read_only%”;–查询全局变量表数据情况

备份

mysql -uroot -h 主 -p  database >back.sql;

#登陆从库
source back.sql;

stop slave;

reset slave;

reset master;

关键

把 show master status\G File和Position对号入座

CHANGE MASTER TO  

MASTER_HOST='master_host_name',

MASTER_USER='主用户名',

MASTER_PASSWORD='主密码',

MASTER_LOG_FILE='主status File',

MASTER_LOG_POS=主status Position;

开启从库

start slave;

检查状态

show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

故障

sudo ls -l /proc/`pidof mysqld`/fd | wc
MySQL数据库里统计已经打开的文件数查看状态参数
 Open_table_definitions 和 Open_tables

进程

配置文件 /etc/security/limits.conf
检查进程的限制:
cat /proc/`pidof mysqld`/limits|grep "Max open files'

mariadb

https://mariadb.org/download/?t=mariadb&p=mariadb&r=11.3.0

mariadb-11.2.1 mirrors.aliyun.com