User Tools

Site Tools


help:mysql:setup

DATA ANALYTICS REFERENCE DOCUMENT

Document Title:Setting up a MySQL Server
Document No.:1548318976
Author(s):Gerhard van der Linde
Contributor(s):

REVISION HISTORY


Revision


Details of Modification(s)

Reason for modification

Date

By
0 Draft releaseSetting 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.

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

Setup and testing

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

Adding users

# 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)
help/mysql/setup.txt · Last modified: 2020/06/20 14:39 (external edit)