数据库设计(MySQL,ORACLE)
python编程快速上手(持续更新中…)
查看所有用户
默认保存在mysql-user中
创建用户、授权(刷新)
- 创建用户
create user ‘zhangsan’@‘localhost’ identified by ‘123456’;
zhangsan登录
- 授权
- 权限:create drop select insert update…
grant select on python_test_1.* to ‘zhangsan’@‘localhost’;
flush privileges;
- 所有权限:
all privileges;
- 刷新权限
flush privileges;
- 查询权限 show grants for ‘用户名’@‘主机名’
show grants for ‘zhangsan’@‘localhost’;
修改权限与密码???
- 修改用户权限
grant select,update on python_test_1.* to ‘zhangsan’@‘localhost’ with grant option;;
flush privileges;
-
修改用户密码
-
知道密码,改新密码
alter user ‘用户名’@‘主机’ identified by ‘新密码’;
alter user ‘zhangsan’@‘localhost’ identified by ‘123’;
- 忘记密码,重置密码
停止服务,设置跳过正常验证,设置密码为空
设置正常验证模式,重启服务,用空密码登录,改成新密码
sudo service mysql stop;
sudo vi /ect/mysql/mysql.conf.d/mysqld.cnf
skip-grant-tables
use mysql
update user set authentication_string=’’ where user=’root’;
sudo vi /ect/mysql/mysql.conf.d/mysqld.cnf #注释
sudo service mysql start;#重启
alter user ‘zhangsan’@‘localhost’ identified by ‘123’; #更新密码
flush privileges;
*远程登录
grand all on . to root’@’%’;
flush privileges;
- 删除用户
drop user ‘lisi’@‘localhost’;
delete from user where user=’’ and host=’’
主从配置
备份与还原windows
1、在主服务器上进行备份
mysqldump -uroot -proot --all-databases --lock-all-tables > ~/master_db.sql
2、在windows上进行数据还原
mysql -uroot -proot < master_db.sql
ubuntu中的mysql
主服务器
1、编辑mysql.conf.d文件,设置log-bin与server-id
sudo vi /etc/mysql/mysql.conf.d/mysql.conf
#追加
log-bin=mysql-bin
binlog_format=mixed
server-id=01
2、重启服务器
sudo service mysql restart
3、登录服务器,创建用户从服务器同步数据使用账号
grant rerication salve on . TO ‘slave’@’%’ identified by ‘salve’;
flush privileges;
4、授权主二进制日志信息
show master status;
从服务器(windows)
1、找到windows的mysql配置文件
2、编辑mysql.conf,
log-bin=mysql-bin
binlog_format=mixed
server-id=02
3、重启服务
4、进入win的mysql,设置连接主服务器
change master to master_host = ‘192.168.121.12’, master_user=’slave’, master_password=’slave’, master_log_file=’mysql-bin.0001’, master_log_ pos=100
5、开启从服务器
start slave;
show slave status \G;
看到yes,表示配置成功
Slave_IO_Running:yes
Slave_Sql_Running:yes
测试同步
改变主,从同步