MySQL I : Setting the root password

1. By default, when you install MySQL, no password is set. This is considered insecure. On Windows, the easiest way to initially change the username and password is to run “Winmysqladmin.exe”. We did this, but canceled the setup user / password dialog. We will use instead the command line method.
2. MySQL users are different from operating system users. Your database can have many users. The master MySQL user is ‘root’, which is different from the Linux ‘root’ user or Windows ‘administrator’.
3. Passwords are case sensitive.
4. To set the initial ‘root’ user password, on Windows and Linux/UNIX, open a command shell.
5. Windows: cd c:\mysql\bin
Linux: cd /usr/local/mysql/bin
6. Set the password: mysqladmin –u root password “mynewpassword”
– and Press Enter. You will not be prompted for a password. Since this is a brand new installation, you do not have a password yet.
7. Test your new root password. Type: mysql –u root –p
And type your password: mynewpassword
8. You are in the MySQL Monitor client. Type: quit
9. Try entering a bad password, and seeing what happens. Type:
C:\mysql\bin>mysql -u root –p
Enter password: abc

ERROR 1045: Access denied for user: ‘root@localhost’ (Using password: YES)
C:\mysql\bin>

Note that the root account is tied to the “localhost” loopback address (root@localhost)
10. You can change the root user’s password (be careful!) by typing:
mysqladmin –u root –p password “mypass”
And type your old password: mynewpassword
– The –p argument makes mysqladmin prompt you for the current (old) password.
11. Test your new root password. Type: mysql –u root –p
And type your password: mynewpassword
12. You are in the MySQL Monitor client. Type: quit
13. Don’t lose your root password. If you do, you can follow the method to reset your password described in Appendix A of your book. (optional: Page 282)
14. To check if MySQL is running type:
mysqladmin –u root –p ping
15. To check which version of MySQL you are running type:
mysqladmin –u root –p version

MySQL I : Installation

Getting Started: Visit www.mysql.com

  • Brand new: Version 5
  • MaxDB: the SAP/MySQL joint venture

Find the Downloads page.

Note available versions.

Click through to the version 4.1 page.

Note the mention of MD5 checksums; follow this link for more information.

Windows installation

Log in as Administrator.

Copy the MD5 checksum to a text file and save it.

Find and download the Windows (X86) package (not the Windows Essentials package).

Unzip the zip file and install the package.

Install to C:\mysql

Do not register with mysql.com, but do perform the initial server configuration.

Open the W2K/NT Services console (Start > Settings > Control Panel > Administrative Tools > Services). Locate the new MySQL service. Note that is not started. For now, do not start the service.

Linux installation

Log in as root.

Find Linux x86 RPM server download.

Copy the MD5 checksum to a text file and save it.

Download the package; if you’re working in the GUI, it will download to your desktop or your home. Double-click the package, or at the command line, command:

rpm -i MySQL<tab> #them press Enter

Find the matching Client program, copy its MD5 checksum and save it, and download the package.

RPM installation will put MySQL in /var/lib/mysql, and add startup scripts to /etc/rc.d/

Installation from a .tar.gz package can be done to /usr/local, /usr/bin, or the location of your choice. Use the command:

./configure –prefix=/usr/local/mysql

to make this choice.

Don’t forget to install the default databases, mysql and test, using the command:

scripts/mysql_install_db

Starting and stopping

Windows, using MySQLAdmin

Run c:\mysql\bin\winmysqladmin.exe. The first time you run WinMySQLAdmin you will be asked to enter a user name and password. Click “Cancel.”

MySQLAdmin will start, and minimize to the System tray. The tray icon looks like a traffic light. Right-click the tray icon to stop and start the service. You can confirm that the service is started by looking at the tray icon for the green traffic light.

Windows, from the command line

cd C:\mysql\bin
mysqld –standalone

Windows, using the Services Panel

Open Start > Settings > Control Panel > Administrative Tools > Services and locate the MySQL service.

Set its startup option to “Automatic.” You can also make startup manual or disabled here.

Linux, from the command line

cd /usr/local/mysql/bin
./safe_mysqld &

This runs a Linux shell script. The “&” tells Linux to run the process in the background. safe_mysqld tells the system to check for other running MySQL processes and shut them down before restarting. Note that you must either be root or the mysql user to start and stop the server this way.

Or, for an RPM installation:

service mysqld start

To configure the mysqld service to start at boot, from an RPM installation:

chkconfig add mysqld #if necessary to add it to chkconfig’s database

chkconfig mysqld up #to set it up to start in run levels 2-5

service mysqld start #to start it immediately

Windows, using MySQLAdmin

Use the System tray icon (the stoplight) to stop MySQL. You will be notified that all connections will terminate. Confirm the service is stopped by looking at the tray icon for the red traffic light.

Windows, using the Services Panel

Open Start > Settings > Control Panel > Administrative Tools > Services and locate the MySQL service. Stop by selecting the service and clicking the start icon in the Services toolbar.

Windows, using the command line

NET STOP mysql

To completely remove the service:

mysqld -nt –remove

Linux, using the command line

cd /usr/local/mysql/bin
mysqladmin shutdown

Note if a user and password has been assigned, you will need this information to shut down the MySQL server:

mysqladmin –u root –p shutdown

Press Enter and enter root’s password at the prompt.

Or, for an RPM installation:

service mysqld stop

Windows

Edit:

C:\WINNT\my.ini or
C:\Windows\my.ini or
C:\mysql\my.cnf

Add this text to what is already there:

basedir=c:/mysql
datadir=c:/mysql/data

Save the file as text.

  • You can change the path to your data here. If you want to store your data on the d: drive, for example, change the path here, and move the data directory and its contents to that location. Make sure the daemon is stopped when you do this.
  • If you have installed the core mysql somewhere other than the default location, you can change this path.
  • The # character is used to indicate comments.
  • Note the UNIX style path delimiter ‘/’

    Restart MySQL using MySQLAdmin.exe.

Linux

Edit:

/etc/my.cnf #the system-wide configuration

/home/username/.my.cnf #a user-level configuration, which will override system config

Save and restart MySQL:

service mysqld restart

Note that on Linux / UNIX MySQL (RedHat variants and Sun) can be auto started on boot by placing a startup shell script in the /etc/rc.d/init.d directory and configuring it as a InitV service. If you install the Redhat or Mandrake RPM, this will be automatically be configured.

MySQL I : Introduction

Instructor: Glenn Norman
Text:

  • A basic understanding of MySQL database functionality
  • Familiarity with construction, maintenance and troubleshooting of databases

What is MySQL?

  • A robust, full-featured enterprise-ready database
  • “Dual-license” software: Open source and Commercial
  • Both “bleeding-edge” (Community Edition) and “Certified Software” (Network Edition)

What will we do in this course?

  • Look at the MySQL AB web site and discuss versions of MySQL
  • Download and install MySQL on Windows and Linux
  • Learn to start and stop MySQL
  • Configure MySQL
  • Use MySQL Client
  • Set up and manage users and configure privileges
  • Learn basic SQL actions in MySQL: select, add, edit, delete
  • Perform joins
  • Learn about MySQL Functions
  • Import data
  • Back up data
  • Manage connections
  • View logs
  • Understand transactions and locking