MySQL數(shù)據(jù)庫應(yīng)用實戰(zhàn)教程-第9章--MySQL管理課件

1、MySQL程序設(shè)計第9章 MySQL管理9.1 用戶管理9.2 權(quán)限管理9.3 表空間管理（InnoDB）9.4 備份與還原MySQL程序設(shè)計第9章 MySQL管理9.5 主從同步配置MySQL程序設(shè)計 創(chuàng)建用戶9.1 用戶管理第9章 MySQL管理MySQL程序設(shè)計update mysql.user set host=192.168.1.100 where user=xiaoying; 修改用戶9.1 用戶管理第9章 MySQL管理MySQL程序設(shè)計update mysql.user set authentication_string=password(新密碼) where user=xia

2、ogang and host=localhost; update mysql.user set host=192.168.1.100 where user=xiaoying; GRANT reload ON *.* to root%;update mysql.user set host=192.168.1.100 where user=xiaoying; 刪除用戶9.1 用戶管理第9章 MySQL管理MySQL程序設(shè)計 DELETE FROM mysql.user WHERE user=xiaoying and host=localhost; 授予權(quán)限：GRANT ALL PRIVILEGES

3、 ON *.* TO xiaogang% IDENTIFIED BY 123456 WITH GRANT OPTION;撤銷權(quán)限：REVOKE ALL PRIVILEGES ON *.* FROM xiaogang% ; 權(quán)限授予語法 授予權(quán)限： GRANT ALL PRIVILEGES ON *.* TO xiaogang% IDENTIFIED BY 123456 WITH GRANT OPTION; 撤銷權(quán)限： REVOKE ALL PRIVILEGES ON *.* FROM xiaogang% ;9.2 權(quán)限管理第9章 MySQL管理MySQL程序設(shè)計授予權(quán)限：GRANT ALL P

4、RIVILEGES ON *.* TO xiaogang% IDENTIFIED BY 123456 WITH GRANT OPTION;撤銷權(quán)限：REVOKE ALL PRIVILEGES ON *.* FROM xiaogang% ; 賬號權(quán)限體系（1）服務(wù)級用戶權(quán)限： GRANT ALL PRIVILEGES ON *.* TO xiaogang% IDENTIFIED BY 123456 WITH GRANT OPTION;（2）數(shù)據(jù)庫級用戶權(quán)限： GRANT ALL PRIVILEGES ON staff.* TO xiaogang% IDENTIFIED BY 123456 WIT

5、H GRANT OPTION;（3）表級用戶權(quán)限： GRANT ALL PRIVILEGES ON staff.employee TO xiaogang% IDENTIFIED BY 123456 WITH GRANT OPTION;（4）字段級用戶權(quán)限：9.2 權(quán)限管理第9章 MySQL管理MySQL程序設(shè)計授予權(quán)限：GRANT ALL PRIVILEGES ON *.* TO xiaogang% IDENTIFIED BY 123456 WITH GRANT OPTION;撤銷權(quán)限：REVOKE ALL PRIVILEGES ON *.* FROM xiaogang% ; 共享表空間 獨占

6、表空間9.3 表空間管理（InnoDB）第9章 MySQL管理MySQL程序設(shè)計授予權(quán)限：GRANT ALL PRIVILEGES ON *.* TO xiaogang% IDENTIFIED BY 123456 WITH GRANT OPTION;撤銷權(quán)限：REVOKE ALL PRIVILEGES ON *.* FROM xiaogang% ; 備份數(shù)據(jù) MySQL數(shù)據(jù)庫備份可以分為熱備份、溫備份、冷備份； 熱備份：當(dāng)數(shù)據(jù)庫進行備份時，數(shù)據(jù)庫的讀寫操作不受影響； 溫備份：當(dāng)數(shù)據(jù)庫進行備份時，數(shù)據(jù)庫的讀操作可以進行，但是不能進行寫操作； 冷備份：當(dāng)數(shù)據(jù)庫進行備份時，數(shù)據(jù)庫不可以進行讀寫操作；

7、9.4 備份與還原第9章 MySQL管理MySQL程序設(shè)計授予權(quán)限：GRANT ALL PRIVILEGES ON *.* TO xiaogang% IDENTIFIED BY 123456 WITH GRANT OPTION;撤銷權(quán)限：REVOKE ALL PRIVILEGES ON *.* FROM xiaogang% ; 備份命令9.4 備份與還原第9章 MySQL管理MySQL程序設(shè)計授予權(quán)限：GRANT ALL PRIVILEGES ON *.* TO xiaogang% IDENTIFIED BY 123456 WITH GRANT OPTION;撤銷權(quán)限：REVOKE ALL P

8、RIVILEGES ON *.* FROM xiaogang% ; 還原數(shù)據(jù) source E:/mysql_bak/201802/staff_20180201.sql;9.4 備份與還原第9章 MySQL管理MySQL程序設(shè)計授予權(quán)限：GRANT ALL PRIVILEGES ON *.* TO xiaogang% IDENTIFIED BY 123456 WITH GRANT OPTION;撤銷權(quán)限：REVOKE ALL PRIVILEGES ON *.* FROM xiaogang% ; 主數(shù)據(jù)庫配置 server-id=1 #主庫和從庫需要不一致 log-bin=mysql-bin #

9、開啟log-bin二進制日志文件 binlog-do-db=db_test #需要同步的數(shù)據(jù)庫 binlog-ignore-db=staff #不需要同步的數(shù)據(jù)庫9.5 主從同步第9章 MySQL管理MySQL程序設(shè)計授予權(quán)限：GRANT ALL PRIVILEGES ON *.* TO xiaogang% IDENTIFIED BY 123456 WITH GRANT OPTION;撤銷權(quán)限：REVOKE ALL PRIVILEGES ON *.* FROM xiaogang% ; 從數(shù)據(jù)庫配置 server-id=2 #主庫和從庫需要不一致 log-bin=mysql-bin #開啟log-bin二進制日志文件 replicate-do-db=db_test