|
| 1 | +## MariaDB 安装及配置 |
| 2 | + |
| 3 | +[官方下载地址](https://downloads.mariadb.org/mariadb/repositories/#mirror=opencas&distro=Ubuntu&distro_release=trusty--ubuntu_trusty&version=10.1) |
| 4 | + |
| 5 | +安装过程 |
| 6 | +``` |
| 7 | +$ sudo apt-get install software-properties-common |
| 8 | +$ sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db |
| 9 | +$ sudo add-apt-repository 'deb [arch=amd64,i386] http://mirrors.opencas.cn/mariadb/repo/10.1/ubuntu trusty main' |
| 10 | +$ sudo apt-get update |
| 11 | +$ sudo apt-get install mariadb-server |
| 12 | +``` |
| 13 | + |
| 14 | +MariaDB 服务 |
| 15 | +``` |
| 16 | +$ sudo /etc/init.d/mysql stop |
| 17 | +$ sudo /etc/init.d/mysql start |
| 18 | +$ sudo /etc/init.d/mysql restart |
| 19 | +``` |
| 20 | + |
| 21 | +终端连接客户端 |
| 22 | +``` |
| 23 | +$ mysql -uroot -p |
| 24 | +``` |
| 25 | + |
| 26 | +设置MariaDB允许远程访问 |
| 27 | + |
| 28 | +使用nestat命令查看3306端口状态： |
| 29 | +``` |
| 30 | +$ netstat -an | grep 3306 |
| 31 | +tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN |
| 32 | +``` |
| 33 | +从结果可以看出3306端口只是在IP 127.0.0.1上监听，所以拒绝了其他IP的访问。 |
| 34 | + |
| 35 | +解决方法：修改/etc/mysql/my.cnf文件。打开文件，找到下面内容： |
| 36 | +``` |
| 37 | +# Instead of skip-networking the default is now to listen only on |
| 38 | +# localhost which is more compatible and is not less secure. |
| 39 | +bind-address = 127.0.0.1 |
| 40 | +``` |
| 41 | + |
| 42 | +把上面这一行注释掉或者把127.0.0.1换成合适的IP，建议注释掉。 |
| 43 | + |
| 44 | +重新启动后，重新使用netstat检测： |
| 45 | +``` |
| 46 | +$ netstat -an | grep 3306 |
| 47 | +tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN |
| 48 | +``` |
| 49 | + |
| 50 | +现在使用下面命令测试： |
| 51 | +``` |
| 52 | +$ mysql -h 192.168.0.101 -u root -p |
| 53 | +Enter password: |
| 54 | +ERROR 1130 (00000): Host 'Ubuntu-Fvlo.Server' is not allowed to connect to this MySQL server |
| 55 | +``` |
| 56 | +结果出乎意料，还是不行。 |
| 57 | + |
| 58 | +解决方法：原来还需要把用户权限分配各远程用户。 |
| 59 | + |
| 60 | +登录到mysql服务器，使用grant命令分配权限 |
| 61 | +``` |
| 62 | +$ mysql -uroot -p |
| 63 | +``` |
| 64 | +查看权限 |
| 65 | +``` |
| 66 | +MariaDB [(none)]> SHOW GRANTS \G |
| 67 | +*************************** 1. row *************************** |
| 68 | +Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
| 69 | +*************************** 2. row *************************** |
| 70 | +Grants for root@localhost: GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
| 71 | +2 rows in set (0.09 sec) |
| 72 | +``` |
| 73 | +添加权限 |
| 74 | +``` |
| 75 | +MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'zhanghe'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; |
| 76 | +Query OK, 0 rows affected (0.68 sec) |
| 77 | +MariaDB [(none)]> FLUSH PRIVILEGES; |
| 78 | +Query OK, 0 rows affected (0.30 sec) |
| 79 | +``` |
| 80 | +重启服务 |
| 81 | +``` |
| 82 | +$ sudo /etc/init.d/mysql restart |
| 83 | +``` |
| 84 | +测试服务器本地访问 |
| 85 | +``` |
| 86 | +$ mysql -p123456 |
| 87 | +MariaDB [(none)]> |
| 88 | +``` |
| 89 | +测试远程访问 |
| 90 | +``` |
| 91 | +$ mysql -h 192.168.0.101 -u root -p |
| 92 | +``` |
0 commit comments