基础操作

递归查询数据表

SELECT distinct * FROM (
    SELECT id, parent_id,group_name FROM daily_work_group_info  WHERE id = 25
    UNION ALL
    SELECT i.id, i.parent_id,group_name
    FROM daily_work_group_info  i
    INNER JOIN (
        SELECT * FROM (
            SELECT id, parent_id FROM daily_work_group_info  WHERE id = 25
            UNION ALL
            SELECT id, parent_id FROM daily_work_group_info WHERE parent_id = 25
        ) t1
    ) t2 ON i.parent_id = t2.id
) t3

主从复制功能

主服务器配置

配置my.ini文件

[mysqld] 
log-bin=mysql-bin
server-id=113
binlog-do-db=czc

创建用户

CREATE USER 'slave01'@'%';

ALTER USER 'slave01'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'slave01'@'%';

flush privileges;

SHOW MASTER STATUS

从服务器配置

配置my.ini

[mysqld]
server-id=206
log-bin=mysql-bin

配置同步

CHANGE MASTER TO

MASTER_HOST = '10.83.130.113',

MASTER_PORT = 3306,

MASTER_USER = 'slave01',

MASTER_PASSWORD = '123456',

MASTER_LOG_FILE = 'mysql-bin.000011',

MASTER_LOG_POS = 1533;

查看状态

SHOW SLAVE STATUS
 show master status
stop slave;# 关闭
start slave;# 开启
reset slave;# 重新配置

问题处理

Slave_SQL_Running:No

处理方案

基础操作

锁表

flush tables with read lock
unlock tables