As root, create a database and grant permissions to a new userTo connect to the mysql client as root
mysql -u root -pYou'll be prompted for the password.
How to create a new user
Create a databaseCREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
create database databasenameConnect to a database
connect databasenameGrant permissions to a user
Then you can log out of mysqlGRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
As a userTo connect to the mysql client as a user
mysql -u username -pIf you have the correct privileges, you can also create a database with a pipe. How to create a database
echo "create database databasename" | mysql -u username -pLoad a dump
cat file.sql | mysql -u username -p databasename
Graphical clientOn Ubuntu, I used mysql navigator by shi bok jang.
On Debian, I used mysql workbench
More on usersChange password, as the user itself or as root:
As root , list all usersSET PASSWORD FOR 'Karl'@'localhost' = PASSWORD('
SELECT User FROM mysql.user;As root , delete a user
drop user Rasdfas@localhost;MySQL documentation on adding user accounts.
More on databasesCommands below work if your shell user name is the same as your mysql user name. If it's dufferent add -u username to the command.
List all databases (in mysql client)
show databasesDelete a database (in the mysql client)
Rename a database (in the shell)DROP database databasename;
After you have verified that everything is in order
mysqladmin -p create new_database mysqldump -p old_database | mysql -p new_database
drop database old_database
Move a table from one database to another
Back up only part of a database with the where option
mysqldump -p database_1 table_name | mysql -p database_2
mysqldump -p -w"productcode=440799" tradeflows raw_flow_yearly > sawnwood99raw.sql
Configuration file my.cnfIt's not desirable to share user name and password in software disseminated over the internet. User name, password and database names can be placed under groups in the configuration file. For example for a given project enter this group in ~/.my.cnf
user = userThen simply specify group=project_name to the client that accesses the database.
password = password
host = localhost
database = dbname