读书笔记

 
数据字典放置到 iblog下的 ibdata中
 
第一个非空唯一索引为主键
 
mysql> show variables like '%connect%';
+-----------------------------------------------+----------------+
| Variable_name                                 | Value          |
+-----------------------------------------------+----------------+
| character_set_connection                      | gbk            |
| collation_connection                          | gbk_chinese_ci |
| connect_timeout                               | 10             |
| disconnect_on_expired_password                | ON             |
| init_connect                                  |                |
| max_connect_errors                            | 100            |
| max_connections                               | 8192           |
| max_user_connections                          | 8000           |                         最大连接数
| performance_schema_session_connect_attrs_size | 512            |
+-----------------------------------------------+----------------+
 
 
 
 
[root@localhost softwares]# mysqld --verbose --help|grep my.cnf
2019-02-27 14:29:14 0 [Note] mysqld (mysqld 5.6.30-log) starting as process 29901 ...
2019-02-27 14:29:14 29901 [Note] Plugin 'FEDERATED' is disabled.
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
                      my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default
2019-02-27 14:29:14 29901 [Note] Binlog end
2019-02-27 14:29:14 29901 [Note] Shutting down plugin 'MyISAM'
2019-02-27 14:29:14 29901 [Note] Shutting down plugin 'CSV'
 
MySQL关闭脚本
mysqladmin --socket=/xxxx.sock  --port=3306 shutdown &
 
 
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
 
mysql> show grants for replusr@'%';
+---------------------------------------------------------------------------------------------------------------------------+
| Grants for replusr@%                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT SUPER, REPLICATION SLAVE ON *.* TO 'replusr'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
 
 
线上备份 必须添加参数 --single-traceion      --master-data=2    
--single-transaction
--master-data=2  显示change master的相关信息
 
binlog 向下兼容
 
 
 
 
 
 
 
 
 
ACID
A 原子性  独立的操作单元 事务要么全部成功要么全部失败
C 一致性    事务结束后系统状态是一致的；
I   隔离性    一个事务对数据的修改 对其他事务不可见
D 持久性    改动都会被持久化
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
取执行计划
explain 
select ...... from ...  where  ...
 
 
 
 
 
 
 
从库的参数
sql_log_bin 
 
 
 
 
MVCC
 
 
MySQL优化原则
  1.   把数据缓存，比如导到 redis 数据库等
  1.   减少交互次数，尽量一次连接 多个操作
  2.   max_used_connections  最大连接数
 
 
 
 
 
 
 
 
 
 
创建时间：2021-12-22 11:39
넶浏览量：0
ꄴ前一个： 无
ꄲ后一个： 无