从外部流浪者连接到MySQL

Question

我想用mycli连接到在流浪实例中运行的MySQL服务器。

我的基本Vagrantfile看起来像下面的代码片段：

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.ssh.shell = "bash -c 'BASH_ENV=/etc/profile exec bash'"

  config.vm.box = "debian/wheezy64"

  config.vm.provision :shell, path: "misc/bootstrap.sh"
  config.vm.network "forwarded_port", guest: 80, host: 8082

  config.vm.synced_folder ".", "/var/www/",
    owner: "vagrant",
    group: "www-data",
    mount_options: ["dmode=775,fmode=664"],
    create: true

  config.vm.provider "virtualbox" do |vb|
      vb.memory = 1536
      vb.cpus = 1
  end
end

为了安装必要的软件，我使用以下代码片段：

echo "mysql-server mysql-server/root_password password $DBPASSWD" | debconf-set-selections
echo "mysql-server mysql-server/root_password_again password $DBPASSWD" | debconf-set-selections

echo "phpmyadmin phpmyadmin/dbconfig-install boolean true" | debconf-set-selections
echo "phpmyadmin phpmyadmin/app-password-confirm password $DBPASSWD" | debconf-set-selections
echo "phpmyadmin phpmyadmin/mysql/admin-pass password $DBPASSWD" | debconf-set-selections
echo "phpmyadmin phpmyadmin/mysql/app-pass password $DBPASSWD" | debconf-set-selections
echo "phpmyadmin phpmyadmin/reconfigure-webserver multiselect none" | debconf-set-selections

sudo apt-get install -y apache2 libapache2-mod-php5 mysql-server php5-mysql php5 phpmyadmin php5-cli php5-curl vim
sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/sites-available/phpmyadmin.conf
sudo ln -s /etc/apache2/sites-available/phpmyadmin.conf /etc/apache2/sites-enabled/phpmyadmin.conf

sudo a2enmod rewrite
sudo a2enmod php5
sudo cp /var/www/misc/apache2.conf /etc/apache2/sites-available/default

sudo service apache2 restart

mysql -uroot -p$DBPASSWD -e "CREATE USER '$DBUSER'@'localhost' IDENTIFIED BY '$DBPASSWD';GRANT ALL ON *.* TO '$DBUSER'@'localhost';FLUSH PRIVILEGES;CREATE DATABASE $DBNAME;"
mysql -u$DBUSER -p$DBPASSWD $DBNAME < /var/www/db.sql

我认为我的问题在于我存储在数据库中的用户凭证。但我想不出答案。

Answer 1

如果您从vm外部进行连接，则需要添加一个端口映射，用于侦听主机并将请求转发到来宾。类似于这里为http定义的：

config.vm.network "forwarded_port", guest: 80, host: 8082

对于mysql (假设您的mysql侦听默认端口)：

config.vm.network "forwarded_port", guest: 3306, host: 33060

之后，修改Vagrant文件vagrant reload。

然后，在连接时，指定端口

mysql -P 33060 -u user -p database

Answer 2

除了JRD所说的端口转发之外，您还需要确保mysql侦听所有端口，而不仅仅是本地端口

编辑/etc/mysql/my.cnf文件，并确保

你已经注释了#bind-address ...这行bind-address = 0.0.0.0

• or

请确保在更改后重新启动mysql服务器

$ sudo service mysql restart

然后，您可以从您的主机进行连接-以防出现以下错误

$ mysql -h127.0.0.1 -P 33600 -uroot -p
Enter password:
ERROR 1130 (HY000): Host '172.16.42.2' is not allowed to connect to this MySQL server

然后回到客人身边，做了

vagrant@precise64:~$ mysql -h127.0.0.1 -uroot -p
...
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.42.2' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

那么，从主机进行连接应该没有问题

$ mysql -h127.0.0.1 -P 33600 -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.44-0ubuntu0.12.04.1 (Ubuntu)