mysql 主库有数据通过锁库做主从
2024-08-31 03:09:55
master@localhost[(none)]> grant replication slave on *.* to 'repl'@'192.168.1.177' identified by 'xxx';
Query OK, rows affected (0.01 sec) --全局读锁
master@localhost[(none)]> flush tables with read lock;
Query OK, rows affected (0.02 sec) master@localhost[(none)]> system pwd
/data/inst3406 --获取master binlog位置
master@localhost[(none)]> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| inst3406bin. | | | | |
+--------------------+----------+--------------+------------------+-------------------+
row in set (0.00 sec) --使用mysqldump导出实例
master@localhost[(none)]> system mysqldump -uroot -pxxx -S /tmp/mysql3406.sock --routines --all-databases --opt >alldb.sql master@localhost[(none)]> system ls
alldb.sql data3406 --解锁
master@localhost[(none)]> unlock tables; master@localhost[(none)]> exit --从库上面导入dump
[mysql@app inst3406]$ mysql -uroot -pxxx -S /tmp/mysql3506.sock <alldb.sql --从库上设置主库的相关信息(host,port等等)
[mysql@app inst3506]$ mysqls
slave@localhost[(none)]> change master to
-> MASTER_HOST='192.168.1.177',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='xxx',
-> MASTER_PORT=,
-> MASTER_LOG_FILE='inst3406bin.000001',
-> MASTER_LOG_POS=;
Query OK, rows affected, warnings (0.01 sec) --启动slave
slave@localhost[(none)]> start slave;
部分从库
master@localhost[(none)]> grant replication slave on *.* to 'repl'@'192.168.1.177' identified by 'xxx';
Query OK, rows affected (0.01 sec) --全局读锁
master@localhost[(none)]> flush tables with read lock;
Query OK, rows affected (0.02 sec) master@localhost[(none)]> system pwd
/data/inst3406 --获取master binlog位置
master@localhost[(none)]> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| inst3406bin. | | | | |
+--------------------+----------+--------------+------------------+-------------------+
row in set (0.00 sec) --使用mysqldump导出实例
master@localhost[(none)]> system mysqldump -uroot -pxxx -S /tmp/mysql3406.sock --routines --all-databases --opt >alldb.sql master@localhost[(none)]> system ls
alldb.sql data3406 --解锁
master@localhost[(none)]> unlock tables; master@localhost[(none)]> exit --从库上面导入dump
[mysql@app inst3406]$ mysql -uroot -pxxx -S /tmp/mysql3506.sock <alldb.sql --从库上设置主库的相关信息(host,port等等)
[mysql@app inst3506]$ mysqls
slave@localhost[(none)]> change master to
-> MASTER_HOST='192.168.1.177',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='xxx',
-> MASTER_PORT=,
-> MASTER_LOG_FILE='inst3406bin.000001',
-> MASTER_LOG_POS=;
Query OK, rows affected, warnings (0.01 sec) --启动slave
slave@localhost[(none)]> start slave;
最新文章
- WPF 自定义模板 Button闪亮效果
- VirtualBox上搭建Ubuntu开发环境
- RMAN备份失败: ORA-19502 & ORA-27072: File I/O error
- BZOJ 1070: [SCOI2007]修车 [最小费用最大流]
- 小记：事务(进程 ID 56)与另一个进程被死锁在 锁 | 通信缓冲区 资源上，并且已被选作死锁牺牲品。
- a letter and a number
- 一行代码解决各种IE兼容问题,IE6,IE7,IE8,IE9,IE10（转）
- 后台调用外部程序的完美实现（使用CreateDesktop建立隐藏桌面）
- 【又长见识了】C#异常处理，try、catch、finally、throw
- jQuery与js的length属性
- UVA11419 SAM I AM
- delete web server(nginx+apache)
- DOM---节点关系
- 微信小程序获取复选框全选，反选选中的值
- spring boot Tomcat访问日志
- vue2.x 父组件监听子组件事件并传回信息
- LeetCode - Partition Labels
- bzoj千题计划198：bzoj1084: [SCOI2005]最大子矩阵
- jQuery UI 给button添加ID
- 网页图表Highcharts实践教程标之加入题副标题版权信息