Author Archives: Vyacheslav

Here is an example of changing the MySQL encoding of a database and tables.
Before any actions on important data it is necessary to make a backup copy, for example:

mysqldump -u USER -h localhost -p BASE | gzip -c > backup_base_`date +%Y-%m-%d`.sql.gz

For the test, we will connect to MySQL and create a couple of new databases without specifying the encoding and specifying:

mysql -u root -p
CREATE DATABASE test_db1;
CREATE DATABASE test_db2 CHARACTER SET utf8 COLLATE utf8_general_ci;

Create a test table in the first database and see its encoding:

USE test_db1;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

show table status like 'users';

Create a test table in the second database and see its encoding:

USE test_db2;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

show table status;

Let’s also look at the encoding of both databases:

SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db1";
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db2";

To see the encoding of a column in a specific table, you can do this:

SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "test_db1"
AND table_name = "users"
AND column_name = "firstname";

In my case, the table in the first database was encoded with latin1_swedish_ci, since it is standard, and in the second one utf8_general_ci, since I specified it beforehand.

You can see the table of possible encodings by such requests:

show collation;
show collation like 'utf8%';
show collation like 'latin1%';

View existing databases as follows:

show databases;

View existing tables in the database:

USE test_db1;
show tables;

Now we change the encoding of the first database and its tables to utf8 and immediately check:

ALTER DATABASE `test_db1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test_db1;
ALTER TABLE `test_db1`.`users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
show table status;

If you need to change the encoding in a sql file, open it in the Notepad ++ editor, for example, convert it to UTF-8/without BOM, and if the encoding in SET NAMES is specified at the beginning of the file, change it there, then you can import the file into the database.

To create a user, we first connect to the MySQL server console:

mysql

Let’s see what users are:

select * from mysql.user;
select user,host from mysql.user;

Create a user (where localhost is specified from where the user can connect, you can specify the IP address, localhost – from the local machine where the MySQL server itself, or % from any addresses):

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

If you intend to connect not only locally, you need to comment out the line in my.cnf:

#bind-address = 127.0.0.1

And restart the MySQL server:

sudo service mysql restart

After that, I recommend restricting access to MySQL using IPTables.
See also – Configuring IPTables

To assign the newly created user unlimited permissions to a specific database, execute the following command:

GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';

If necessary on all bases:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

You can specify specific access rights:

GRANT SELECT ON database_name.* TO 'user'@'localhost';
GRANT SELECT, INSERT ON database_name.table_name TO user@192.168.1.5;

If you want to create a new database:

CREATE DATABASE database_name;

For the changes to take effect, execute:

FLUSH PRIVILEGES;

You can delete the user as follows:

DROP USER 'user'@'localhost';

Example of viewing privileges:

SHOW GRANTS FOR 'user'@'localhost';
SHOW GRANTS;
SELECT * FROM information_schema.user_privileges;