Tag: mysql

Create A New MYSQL Database and User

Here is a quick guide to creating a new MYSQL database, adding a user, and then finally a simple table. I will be using debian/ubuntu as the host system.

First create the database.

sudo mysqladmin create testdatabase

sudo mysql -u root

Create the database user.

CREATE USER `test_user`@`localhost` IDENTIFIED BY 'password';

Now grant the new user permissions on the new database.

GRANT ALL ON testdatabase.* to `test_user`@`localhost`;

Don’t forget to flush privileges!

FLUSH PRIVILEGES;

Now login to your new user/database.

mysql -u test_user -p testdatabase

Now we can create a test table.

CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` tinytext COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `url` text COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Lets get the description of the table to make sure it worked.

mysql> desc members;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int      | NO   | PRI | NULL    | auto_increment |
| name        | tinytext | NO   |     | NULL    |                |
| description | text     | NO   |     | NULL    |                |
| address     | text     | NO   |     | NULL    |                |
| url         | text     | NO   |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql>

Finally, lets add a quick row.

mysql> INSERT INTO members(name, description, address, url) VALUES('Matt', 'Hackerman', '1234 Main St.','https://pghcpc.com');
Query OK, 1 row affected (0.01 sec)

Run a select query now to show your new row.

mysql> select * from members;
+----+------+-------------+---------------+--------------------+
| id | name | description | address       | url                |
+----+------+-------------+---------------+--------------------+
|  1 | Matt | Hackerman   | 1234 Main St. | https://pghcpc.com |
+----+------+-------------+---------------+--------------------+
1 row in set (0.00 sec)

That’s it! Have fun with your new database!

February 5, 2022

Set Timezone for MYSQL / PHP
After setting system time, I discovered that mysql and php services didn’t also update the timezone settings. Here’s how to make sure they also reflect the correct timezone.

First locate all the php.ini files in /etc
```
cd /etc/
find . | grep php.ini
./php/7.4/apache2/php.ini
./php/7.4/cli/php.ini
```
now edit these files and add the timezone info
```
nano ./php/7.4/apache2/php.ini
```
add this to the [Date] block:
```
[Date]
date.timezone = "America/New_York"
```
For mysqld portion, first copy timezone data into mysql tables:
```
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
```
Now edit the mysqld.cnf file:
```
nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
And add this to the [mysqld] block:
```
[mysqld]
default-time-zone='America/New_York'
```
Finally do a service restart:
```
systemctl restart mysql
systemctl restart apache2
```
That’s It, Enjoy!
September 21, 2021

Tag: mysql

Create A New MYSQL Database and User

Set Timezone for MYSQL / PHP