This section is to assist with the configuration of MySQL database following the information of Ubuntu LAMP server. As you know the database may be used is various capacities to store data. Below are some sets to follow to accomplish your goal:
1. MySQL Bind Address
The purpose of this configuration is to allow remote clients to access the database. If this is not set properly, an error message "Can not connect to mysql error 10061", will be retrieved when trying to access.
> nano /etc/mysql/my.conf
-> Change the line: Bind-address = localhost to your IP address or comment out to automatically retain the host address
2. Set MySQL root password
For security purposes and best practices is always best to change the root password.
> mysql -u root -p (log into the db)
> SET PASSWORD FOR 'root' @ 'localhost' = PASSWORD ('yourpassword');
3. Create your MySQL db
> CREATE DATABASE mydb; (mydb is your db name)
4. Create a user with all privileges
> GRANT ALL PRIVILEGES ON *.* TO 'root' @ 'localhost' IDENTIFIED BY 'yourpwd' WITH GRANT OPTION;
-> The above statement gives the root account all privileges to the all tables in the databases
5. Create a MySQL user with fewer priviliges
> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORY TABLES, LOCK TABLES ON yourdb.* TO 'root' @ 'ip address" IDENTIFIED BY 'yourpwd';
-> This command gives the the user root fewer privileges to all tables in db 'your db'
There are MySQL admin tools that will allow you to administer the database from another node.