Database Setup

Table of Contents

• Installing on macOS
• Installing on Windows
• Installing on Linux

Installing on macOS

Download the .dmg file

Link here: MySQL downloads Notes:

• Make sure to choose the right OS from the dropdown menu
• If it sends you to a page asking you to signup, look below for a small "No thanks, just start my download."

Install

Click the .dmg file, follow the installer instructions. Do NOT forget the password you set for root. Once finished, you should be able to find a control panel for MySQL if you go to "System Preferences" (it's usually a new tile at the bottom)

Configure

1. Make sure your MySQL instance is running. Go to System Preferences > MySQL and you should see a green dot for ACTIVE INSTANCE.
2. You need to alias the mysql and mysqladmin commands so you can use them in the terminal:

$ cd ~
$ nano .zshrc

3. Once you are inside the nano editor, copy and paste the following two lines:

alias mysql="/usr/local/mysql/bin/mysql"
alias mysqladmin="/usr/local/mysql/bin/mysqladmin"

4. To close nano, press control-X, answer yes to changes, and press enter to confirm file name
5. Now you can get into mysql and create a new database. First command will ask for your password. And don't forget to replace db_name with whatever name you want to give your database

$ mysql -u root -p
$ mysql> CREATE DATABASE db_name

Add database details to your .env file

Remember that the .env file is where we keep all the passwords, tokens and other sensitive information. We included this file in .gitignore so you should have your own local copy and it will never be shared (you never should anyways).

Add the following entries to your .env file:

DB_USER=
DB_HOST=
DB_DATABASE=
DB_PASSWORD=

After each = add the corresponding information. Your DB_USER should be root unless you created a new one, DB_HOST is usually localhost, DB_DATABASE is the name you gave your database in the previous step, DB_PASSWORD is the password you set for root (again, unless you set a new user). Also note, you don't need to encapsulate these in " " unless they have spaces!

Change login for your database

Now you need to change the way you login to your database so it will work with our codebase.

1. Login to your dashboard, replacing db_name with the name you gave your db

$ mysql -u root -p db_name

2. Inside mysql, do the following replacing password with your password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Recreate the database locally

In order to make sure your local database has the same structure as the one the live bot uses, do the following:

1. Navigate to the folder with the repo
2. Run the following:

$ npm run db-recreate

3. Now let's make sure everything worked. Log back into your mysql dashboard and check the available tables:

mysql> SHOW TABLES;

You should see something similar to this:

+--------------------+
| Tables_in_db_name  |
+--------------------+
| infractions        |
| mod_log            |
| msg_counter        |
| user_notes         |
| verifications      |
+--------------------+

Congratulations! You are now ready to start using your local database with the repo for testing purposes 👍

---

Installing on Windows

Download the MSI

Link here: MySQL Windows download

Notes:

• Select the "web community" option, which only downloads what you need
• If it sends you to a page asking you to signup, look below for a small "No thanks, just start my download."
• The link above is to the MSI download, not the .zip download, because the MSI takes care of most of the setup for you. Don't attempt to install with the .zip files unless you have advanced knowledge of Windows installation procedures and you don't mind spending 3x the time going through extra steps.

Install MySQL Server

Follow the prompts given by the installer.
You can also check out this video if you are unsure of anything: https://youtu.be/GIRcpjg-3Eg?t=75

If for some reason the installer finishes without having you set up a root user, etc., you can re-open the installer and click the Reconfigure button next to MySQL Server:

Check that MySQL server is running

To make sure your local server is running, use the search bar on your desktop, search for MySQL Workbench and open it. Under "MySQL Connections" you should see a connection called "Local Instance". Click on it to open it up. If you are prompted for your root password, type that in to the pop-up.

On the left-hand side of the page, you should now see a panel with a section called "Management". Click on "Server Status" to see if your server is running. If it is, you should see a big green play button with the word "Running" underneath. If you see a red stop button, go ahead and click it to start the server.

Open MySQL on the command line

If you followed the video posted above, you saw that there is a built-in MySQL Command Line Client for Windows. Using this will work if you only want to use your root user. However, you will need to select your discord bot database each time you login using the command USE db_name.

In order to enable the more traditional MySQL shell, you will need to add the correct folder to your Windows PATH environment variable.

1. Using the search bar on your desktop, type for "environment" and click on "Edit the System Environment Variables".
2. You will be in a window called "System Properties". At the bottom of the page, click the button called Environment Variables
3. In the User Variables section, scroll down and double-click on the "Path" entry.
4. Click New and paste in the path to the folder that contains your mysql.exe file. By default, this should be stored in C:\Program Files\MySQL\MySQL Server 8.0\bin, but you should double-check it is there just to be sure.
5. Hit OK 3 times to get out of all the system properties windows.

Now, when you restart your terminal of choice (Git Bash, PowerShell, CMD, etc.) you will be able to interact with MySQL from the command line using the following command:

$ mysql -u root -p

Set Up Your Database and User

From the command line, you can create a database using the following command: CREATE DATABASE your_db_name;.

Once you have done that, you can verify that the database has been made by using the SHOW DATABASES; command. You should see something like this:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| your_db_name       |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+

Make sure to use the CREATE DATABASE command to make a new database to use for the bot.

If you would also like to make a dedicated user for the bot, you can do so with the following command:

CREATE USER 'your_user_name'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

You will also need to grant them permissions for that particular database with this command:

GRANT ALL PRIVILEGES ON your_database.* TO 'your_username'@'localhost';

Now you should be able sign directly into your discord bot's MySQL database as the dedicated user. To make sure it works, type exit to get out of the MySQL shell and then use the command mysql -u your_username -p your_database_name.

Note:

• If you opted not to make a dedicated user for your database, you will still need to change the password plugin that is used to authenticate your root user, because the Nodejs MySQL package doesn't work with the newest MySQL auth type. To do so, simply sign in to MySQL as root and use the following command:

  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

Set your .env variables and create the database tables

All that's left to do is add your secret keys to your local version of the project and create the database tables!

First, if you haven't created a .env file in your local clone of the repository, do so now. Then, add in the database details in following these instructions.

Finally, recreate the database locally and you are ready to go!

---

Installing on Linux

1. Follow this article to get set up.
2. Inside of the mysql shell, create a database with the command CREATE DATABASE your_db_name;. If you want to use a separate (non-root) user to access this database, follow the instructions in the above article to create a new user.
3. Change the password plugin used for authenticating the user you want to access the database:

    ALTER USER 'your_username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

4. Add the database details to your .env file (see the instructions above)
5. Recreate the database locally and you are ready to go.

---