DATA ANALYTICS REFERENCE DOCUMENT
|Document Title:||Setting up a MySQL Server|
|Author(s):||Gerhard van der Linde|
Details of Modification(s)
Reason for modification
|0||Draft release||Setting up a MySQL Server||2019/01/24 08:36||Gerhard van der Linde|
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.
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.
[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
This is an optional step and not needed when a clean manual install is created since the data folder will not exist yet.
# 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
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.
# create a secure password mysqld --initialize --console #create ablank password mysqld --initialize-insecure --console
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.
# 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
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.
# 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.
Test the connection on the MySQL server using command line.
# test the connection on the command line mysql -u root -p
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.
#shut down the running service in the first console mysqladmin -u root -p shutdown
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.
# this optional command creates a windows service if not already created, # you might have to start this manually mysqld --install
# 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)