MySQL commands and useful stuff for working with this RDBMS.
CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';
Grant permissions to work on database(s):
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost';
Reload privileges so the RDBMS knows about the user:
FLUSH PRIVILEGES;
Login as an administrative user to the mysql client:
$ mysql -u root -pThen, you can list users with:
mysql> select * from mysql.user;
Notice this list all fields from the table. You can filter to get a better overview of the table:
mysql> select host, user, password from mysql.user;
You can always see what fields to list with:
mysql> desc mysql.user;
Backup database:
mysql -u [USER_NAME] -p [DB_NAME] > dump.sql
Recover database from dump:
mysql -u [USER_NAME] -p [DB_NAME] < dump.sql
Once you install MySQL server in a vagrant machine you can access that DB by SSHing into the vagrant box and then login in to the mysql CLI. However, if you'd like to have a visual representation or use a GUI, such a WorkBench, for easy manage of the database, some additional setup is needed.
First, you need to change the bind-address in the mysql configuration file (located in /etc/mysql/my.conf).
Open your mysql conf file:
sudo nano /etc/mysql/my.conf
Find the line with bind-address and change to something like:
bind-address = 0.0.0.0
Restart MySQL server to load new setting:
sudo service mysql restart
Connection Method: Standard TCP/IP over SSH
SSH Hostname: [127.0.0.1:2222] or virtual machine IP and port
SSH Username: vagrant
SSH Password: vagrant
MySQL Hostname: 127.0.0.1
MySQL Server Port: 3306
Username: root
Password: [root-password]
Sometimes you'd need to specify an ssh key file. See full configuration below:
WordPress, for whatever design reason, saves url in two places: database and wp-config.php file. When not saved in config file, not having a correct value in the database causes mayhem in the WordPress installation.
With this query you can find set values for siteurl and home values in the wp_options table of a WordPress installation:
SELECT * FROM [db_name].wp_options
WHERE option_name = 'siteurl'
OR option_name = 'home';