mysql 管理
安装数据库
yum install -y mysql-community-bench.x86_64 mysql-community-client.x86_64 mysql-community-common.x86_64 mysql-community-devel.x86_64 mysql-connector-odbc.x86_64 mysql-community-server.x86_64
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1234' WITH GRANT OPTION; #允许远程登录
flush privileges; #刷新
使用mysql实现增删改查的功能。。。
常用命令
alter table test add city char(8) not null default 'baoding'; #添加城市
alter table test drop column city; #删除
update test set city='beijing' where id='01'; #为表内某值添加city
insert into test (id,name,age) values ('02','han2',21); #插入
select * from test order by age desc; #降序
select * from test order by age asc; #升序
select * from test where age > 21; #查找
update test set age=age+1; #全表内 年龄加一
select host,user,password from user; #查询user，，看用户
create user aaa@localhost identified by '1234'; #为mysql添加用户
rename user aaa@localhost to han@localhost; # 改名
update mysql.user set password=password('123') where user='han'; #修改密码
set password for han=password('123'); #设置密码
drop user aaa@localhost; #删除用户
show grnats for han@localhost; #看用户权限
grant select,create,drop,update,alter on ttt.* to 'han'@'localhost' identified by '123' with grant option; #赋予权限
flush privileges; #刷新
revoke all on *.* from han@localhost; #权限撤销
mysqldump -u root -p ttt > /opt/ttt.bak #数据库备份
mysqldump -u root -p ttt test > /opt/ttt-test.bak #数据库表备份
source /opt/ttt.bak #数据库恢复
破解mysql root密码
然后打开mysql配置文件/etc/my.cnf.在【mysqld】下面添加一行代码：skip-grant-tables。
这行代码意思就是跳过跳过授权表，即是可以跳过密码验证直接进入数据库。
也可以
stop mysql后
mysqld_safe --skip-grant-tables &
mysql
use mysql
update user set password=password('mima') where user='root';
flush privileges;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mysql实现双主机。。。
两台主机a192.168.1.142 b192.168.1.148
a主机配置
进入mysql
create database test;
grant replication slave,file on *.* to 'aaa1'@'192.168.1.148' identified by '123456'; #建立同步用户
flush privileges;
vi /etc/my.cnf
在mysqld加以下
log-bin=mysql-bin
server-id=1 #另一台不一致
binlog-do-db=test
binlog-ignore-db=mysql
replicate_wild_do_table=test.%
#replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1 #other 2
重启数据库
进入数据库锁表
flush tables with read lock;
show master status\G; ＃查看主服务器同步状态
#主辅互为辅助。以下是主服务器设置。
change master to master_host='192.168.1.148', master_user='aaa2', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=120;
flush privileges;
start slave;
unlock tables；
show slave status\G
*****************************
b主机按以上再来一遍 注意id ip名字就可以 *（注意防火墙）
测试
看到以下表示成功 两个yes。。
