mysql操作命令master-to-master
1.select語句中把兩個字符串字段合並成一個字段:
select concat(a, b) as c from tables
2.按日期分組查詢
select DATE_FORMAT( created_at, '%Y-%m-%d %H' ) as t from tables group by t
3.mysql創建用戶/添加遠程/修改用戶密碼
先進入mysql命令:mysql -uroot -proot
->create user username@'192.168.39.%' IDENTIFIED BY 'password';
配置遠程
->GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO username@'192.168.39.%' IDENTIFIED BY 'password';
修改密碼
->update mysql.user set password=PASSWORD('xxx') where user='username';
->flush privileges;
4.master-to-master
先配置遠程:
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO username@'192.168.39.%' IDENTIFIED BY 'password';
建立master:
->slave stop;
->change master to master_host='192.168.0.2',
->master_user='backup',
->master_password='back',
->master_log_file='mysql-bin.000001',
->master_log_pos=106;
->slave start;
->show slave status\G
4.查看server_id,註意master-to-master時,兩個伺服器的server_id不能一樣,需要修改。
->show variables like 'server_id';
修改server_id
->set global server_id=10;
5.查看auto_increment_increment與auto_increment_offset值:
->SHOW VARIABLES LIKE 'auto_inc%';
6.兩個伺服器master-to-master,必須設置auto_increment_offset值為不同,如下:
第一臺:
auto_increment_increment =2 #為開始值
auto_increment_offset = 1
第二臺:
auto_increment_increment =2 #為開始值
auto_increment_offset = 2
7.mysql遠程鏈接權限有以下14個:
select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file