平、MySQ主从复制原理(主库写副数据,从仓库读取数据)
MySql常用命令:
MySQL5.7设置密码 update user set authentication_string=password('123456') where user='root';
MySQL5.6设置密码 UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
使用命令show global variables like 'port';查看端口号
使用“GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;”命令可以更改远程连接的设置。
安装mysql服务命:mysqld install mysql 启动mysql命令:net start mysql
SELECT * FROM USER WHERE USER='用户名';
SHOW VARIABLES LIKE '%log_bin%';
MySql主从同步实战
1.主库配置:
先是步:my.ini配置文件(配置好后记得重开mysql服务)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = test1
binlog-do-db = test2
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
注:
binlog-do-db = test1(需要同的主库的数据库名字)
binlog-do-db = test2(需要共同的主库的数据库名字)
binlog-ignore-db = mysql(需要忽略的之主库的数据库名字)
亚步:给主数据库开启可被从数据库访问的账户
GRANT REPLICATION SLAVE ON *.* TO '用户名'@'%' IDENTIFIED BY '密码';//不建议使用root “%”表示所有客户端都可能连,只要帐号,密码正确,此处可用从服务器IP代替。
2.于仓库配置:
第一步:my.ini配置文件(配置好后记忆重开mysql服务)
[mysqld]
server-id = 2
log_bin = log_bin
亚步:从库连接主数据库进行多少并(使用SHOW MASTER
STATUS指令查看主库的状态,将从仓库的MASTER_LOG_FILE和MASTER_LOG_POS的价修改也主库对应的值)
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO
MASTER_HOST="主数据库IP地址",
MASTER_PORT=3306,
MASTER_USER="用户名",
MASTER_PASSWORD="密码",
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;
START SLAVE;
SHOW SLAVE STATUS;