MySQL with Django¶
Ubuntu: need to install (to use MySQL with Django):
sudo apt-get install mysql-client mysql-server libmysqlclient-dev
Django:
pip install mysqlclient
DATABASES['default'] = {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'dbname',
'USER': 'username',
}
Using the client¶
Starting the client:
$ mysql --user=username [database] # if user has no password
$ mysql --user=username --password [database] # to be prompted for password
To do things that require mysql root:
$ mysql -u root # If root has no password and older Debian
$ mysql -u root -p # if root has password and older Debian
$ sudo mysql -u root # On more recent Debian, no need for root password but must be root user
Examining the data definitions¶
Brief overview of a table’s structure:
mysql> describe auth_user;
+-----------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
+-----------------------------+--------------+------+-----+---------+----------------+
Full table definition:
mysql> show create table auth_user;
show create table auth_user;
+-----------+--...-+
| Table | Create Table ...|
+-----------+-...--+
| auth_user | CREATE TABLE `auth_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
CONSTRAINT ...,
) ENGINE=InnoDB AUTO_INCREMENT=159799 DEFAULT CHARSET=utf8 |
+-----------+-...--+
Users and permissions¶
Cannot login as root¶
See this stackoverflow question, the first answer as of today.
Best answer seems to be to just create another user to use. (This is not quite what the answer on stackoverflow suggests, but I don’t understand how creating a second user with the same problem as root would help.)
I’m going to name the new user toor
in this example (root backwards):
$ sudo mysql -u root # I had to use "sudo" since is new installation
mysql> USE mysql;
mysql> CREATE USER 'toor'@'localhost' IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'toor'@'localhost';
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='toor';
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ service mysql restart
Non-root users¶
In the client:
mysql> SELECT user, host from mysql.user; # List existing users
mysql> CREATE USER 'username' IDENTIFIED BY 'plaintextpassword'; # Create user with password
mysql> CREATE USER 'username'@'localhost'; # no password, can only connect locally
mysql> SHOW DATABASES;
mysql> CREATE DATABASE databasename;
mysql> GRANT ALL ON databasename.* TO "username"@"hostname" IDENTIFIED BY "password";
mysql> FLUSH PRIVILEGES;
mysql> DROP DATABASE databasename;
mysql> DROP USER username;
mysql> EXIT
Bye
Change any user password¶
Note: default host is ‘%’ which will not let you connect via unix socket, must set password for host ‘localhost’ to allow that:
mysql> update mysql.user set password=password('foo'),host='localhost' where user='poirier_wordpres'; # On older MySQL
mysql> set password for 'dpoirier'@'localhost' = 'plainpass'; # More recent MySQL
mysql> flush privileges;
Recover lost password¶
http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html
C.5.4.1.3. Resetting the Root Password: Generic Instructions On any platform, you can set the new password using the mysql client:
Stop mysqld
Restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.
$ mysql
mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> EXIT
Stop the server
Restart it normally (without the --skip-grant-tables and --skip-networking options).
Dumps¶
Make a dump:
mysqldump --single-transaction _dbname_ > dumpfile.sql
mysqldump --result-file=dumpfile.sql --single-transaction _dbname_
(Use --single-transaction
to
avoid locking the DB
during the dump.)
Restore a dump:
mysql dbname < dumpfile.sql
Create a new MySQL database¶
Step by step:
$ mysql -u root -p
<ENTER MYSQL ROOT PASSWORD>
mysql> create user 'ctsv2_TR'@'localhost';
mysql> create database ctsv2_TR;
mysql> grant all on ctsv2_TR.* to 'cstv2_TR'@'localhost';
mysql> flush privileges;
mysql> exit
Bye