上Mysql HA課程 Day1 12/9 DIY

[Master]


Boot MySQL Server

1.server-id

2.datadir *

3.log-bin



[Slave]

Boot MySQL Server

1.server-id

2.datadir *

3.Port *

4.socket * (if in Unix like OS)



[Master]

Create user for replication slave, with password

Grant "REPLICATION SLAVE" privilege to this user



[Slave via client]

Issue 'CHANGE MASTER TO' with option:

1.MASTER HOST

2.MASTER USER

3.MASTER PASSWORD



Issue 'Start SLAVE'

============

command



[Master] PS:start可有可無

mysqld --server-id=1 --datadir=..\data1 --log-bin

start mysqld --server-id=1 --datadir=..\data1 --log-bin

[Slave]

start mysqld --server-id=2 --datadir=..\data2 --port=3307 --log-bin



[Master]

mysql -u root

[mysql root cmd]:CREATE USER 'scott'@'localhost' IDENTIFIED BY 'tiger';

mysql -u scott -p -->for login test,是否被建立? passwd:tiger

[mysql root cmd]:GRANT REPLICATION SLAVE ON *.* TO 'scott'@'localhost';

[mysql root cmd]:CREATE USER 'scott'@'xxx.xxx.xxx.xxx'; PS:允許從其他機器登陸

[mysql root cmd]:use mysql

[mysql root cmd]:SELECT User, Host, Password FROM user;



[Slave]

mysql -u root -P 3307

[mysql root cmd]:CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='scott', MASTER_PASSWORD='tiger';

[mysql root cmd]:start slave;

[mysql root cmd]:SHOW PROCESSLIST\G;

[mysql root cmd m]:use test;

[mysql root cmd m]:show tables;

[mysql root cmd m]:create table foo (id INT, name CHAR(8));

[mysql root cmd m]:INSERT INTO foo VALUES(1,'aaa'), (2,'bbb'), (3,'ccc');

[mysql root cmd]:SHOW slave status\G;

[mysql root cmd]:SHOW master status\G;



mysqlbinlog.exe ..\datax\xxxxxxx.index PS:??

mysqlbinlog.exe ..\datax\xxxxxxx.0000xx

mysqlbinlog.exe ..\datax\xxxxxxx.info PS:??



mysql -u root -P 3307 -e "SHOW SLAVE STATUS"

mysql -u root -P 3307 -B -e "SHOW SLAVE STATUS"

mysql -u root -P 3307 -H -e "SHOW SLAVE STATUS"

mysql -u root -P 3307 -X -e "SHOW SLAVE STATUS"



mysqladmin -u root -P shutdown

mysqladmin -u root -P 3307 shutdown





[mysql root cmd m]:create table bar(id int, name char(8))

ENGINE = BLACKHCLE;

[mysql root cmd m]:insert into bar values(1, 'aaa');

[mysql root cmd s]:Alter table bar engine=MyISAM; PS:要小心

[mysql root cmd m]:create table bar2(id int, name char(8))

ENGINE = BLACKHOLE;

[mysql root cmd m]:insert into bar values(2, 'bbb');

[mysql root cmd m]:select * from bar; PS:有問題, engines被改變

[mysql root cmd m]:show engines \G;