~~NOTOC~~
|<100% 25% - >|
^  \\ DATA ANALYTICS REFERENCE DOCUMENT\\ \\   ^^
^  Document Title:|Setting up a MySQL Server|
^  Document No.:|1548318976|
^  Author(s):|Gerhard van der Linde|
^  Contributor(s):|  |



**REVISION HISTORY**
|< 100% 10% - - 10% 17% 10% >|
^  \\  Revision\\  \\  ^\\ Details of Modification(s)^\\ Reason for modification^  \\ Date  ^  \\ By  ^
|  [[:doku.php?id=mysql:setup&do=revisions|0]]  |Draft release|Setting up a MySQL Server|  2019/01/24 08:36  |  Gerhard van der Linde  |


----

====== MySQL Server Setup ======

===== Download and Install =====

The installers are available for download here:[[https://dev.mysql.com/downloads/mysql/]]

A windows packaeged installer is available. This document describes the manual setup and running of the zipped packages for windows, but also applies almost exactly for all the available paltforms exept for the platform specific commands as specified in the relevant sections.



===== Configuration File =====

After the download is completed, extract the zip archive and create the configuration file in the extracted folder using the path conventions of the relevant OS, the example below shows the setup for the windows platform.

<code MySQL my.ini>
[mysqld]
# set basedir to your installation path
basedir=c:/Users/xxxxx/Downloads/mysql-8.0.14-winx64
# set datadir to the location of your data directory
datadir=c:/Users/xxxxx/Downloads/mysql-8.0.14-winx64/data
# set the authentication type to native password, so not ssh or ssl 
# as this requires aditional downloads and setup steps
default_authentication_plugin=mysql_native_password
</code>

===== Setup and testing =====

<WRAP center round tip 60%>
This is an optional step and not needed when a clean manual install is created since the data folder will not exist yet.
</WRAP>


<code mysql>
# assuming no database created before, you can delete the entire folder and start over. 
# remember all data will be lost doing this.
rm -r data
# initialize the MySQL database to create a folder structure, default is data, 
# specify console mode to see output and eror messages
</code>

This section provides two options to initialize MySQL server and create a new database structure to work with. The first option creates a secure random password. If the "--console" option is not specified the password will not be shows and it would not be possible to access the database without knowing the password.

The second option creates a new database folder with a blank password.

<code mysql>
# create a secure password
mysqld --initialize --console

#create ablank password
mysqld --initialize-insecure --console
</code>

After the database was initialized it can be launched on the command line in console mode. Console mode outputs the log and error messages to the screen and helps with the setup and test process.

If the secure password option was specified above the console must be launched with user and password prompt as show in the commented code. 

<code mysql>
# launch the server in console mode
mysqld --console
# or launch the console specifying root user and -p for a password prompt.
# mysqld -u root -p --console
</code>

The following section is used to amend the password and requires the database server to be running. The database server is launched using MySQLD as shown above. So another console needs to be started to do this. Note that the mysqladmin utility is now used to connect to the running server and that the root user is specified and also specified that we want to amend the password.

<code mysql>
# start a new console and amend the password using mysqladmin
mysqladmin -u root -p password
New password: ****
Confirm new password: ****
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
</code>

Test the connection on the MySQL server using command line.

<code mysql>
# test the connection on the command line
mysql -u root -p
</code>

This step is optional and allows you to shut down the server from the command line. Another way to do this is using "ctrl" and "c" keys in the windows console/command line window running MySQLd server instance.

<code mysql>
#shut down the running service in the first console
mysqladmin -u root -p shutdown
</code>

This last step is optional too and sets up a the current environment created in the steps above as a windows service. Opening windows services console after running this last command will show a MySQL entry launching the MySQLd service as a windows service instead. You might have to start this manually or reboot the PC to start it up automatically.

<code mysql>
# this optional command creates a windows service if not already created, 
# you might have to start this manually
mysqld --install
</code>

==== Adding users ====

<code mysql>
# use localhost instead of % for local connections only
mysql> CREATE USER 'mickey'@'%'; # IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mickey'@'%' WITH GRANT OPTION; 
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

</code>