my sql 101
DESCRIPTION
MySQL basicsTRANSCRIPT
![Page 1: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/1.jpg)
1
MySQL 101MySQL 101MySQL (play /ma skju l/ "My S-Q-L",ɪ ˌɛ ːˈɛ
[3] officially, but also incorrectly called /ma ɪsi kwəl/ "My Sequel") is the world's most ˈ ːused[4] relational database management system (RDBMS)[5] that runs as a server
providing multi-user access to a number of databases. The SQL phrase stands for
Structured Query Language.[7] – Wikipedia
Http://slideshare.net/davestokes/presentations
![Page 2: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/2.jpg)
2
AgendaAgenda➔ Installation➔ Starting MySQL➔ Stopping MySQL➔ Connecting to MySQL➔ Loading data➔ Looking at data➔ Backup➔ Login/Authentication➔ Where to go from here➔ Suggestions➔ Questions and Answers
[email protected] @stoker
![Page 3: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/3.jpg)
3
InstallationInstallation MySQL is available for most Operating
Systems Binaries, RPMS and DEBs available Windows Source Code
http://dev.mysql.com/downloads/ Is what I recommend
http://dev.mysql.com/doc/refman/5.5/en/getting-mysql.html
![Page 4: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/4.jpg)
4
PackagesPackages sudo apt-get install mysql5 rpm -Uhv mysql5
May have slightly different names, may have client utilities separate
http://dev.mysql.com/doc/refman/5.5/en/linux-installation.html
![Page 5: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/5.jpg)
5
BinariesBinariesshell> groupadd mysqlshell> useradd -r -g mysql mysqlshell> cd /usr/localshell> tar zxvf /path/to/mysql-VERSION-OS.tar.gzshell> ln -s full-path-to-mysql-VERSION-OS mysqlshell> cd mysqlshell> chown -R mysql .shell> chgrp -R mysql .shell> scripts/mysql_install_db --user=mysqlshell> chown -R root .shell> chown -R mysql data# Next command is optionalshell> cp support-files/my-medium.cnf /etc/my.cnfshell> bin/mysqld_safe --user=mysql &# Next command is optionalshell> cp support-files/mysql.server /etc/init.d/mysql.server
http://dev.mysql.com/doc/refman/5.5/en/linux-installation.html
![Page 6: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/6.jpg)
6
Configuration FilesConfiguration Filesshell> cp support-files/my-medium.cnf /etc/my.cnf
The configuration files have not aged well. They were written long ago. Please read through the choices to find one that best matches your system.
MySQL will use built-it defaults, almost guaranteed not to be optimal for your environment if no my.cnf is present
http://dev.mysql.com/doc/refman/5.5/en/binary-installation.html
![Page 7: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/7.jpg)
7
Starting MySQLStarting MySQLshell> bin/mysqld_safe --user=mysql & This command is what starts your MySQL server. It runs a wrapper script as user mysql in the background. You might also see this wrapper wrapped
/etc/init.d/mysql start or service mysql start
http://dev.mysql.com/doc/refman/5.5/en/binary-installation.html
![Page 8: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/8.jpg)
8
mysqldmysqld What is that wrapper running? The mysqld
binary
/usr/local/mysql/bin/mysqld –user=mysql
(And other options from /etc/mysql/my.cnf)
http://dev.mysql.com/doc/refman/5.5/en/mysqld.html
![Page 9: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/9.jpg)
9
/etc/mysql/my.cnf/etc/mysql/my.cnf Options for your
MySQL Server Please use as CLI
options are not good for long term sanity!
Use your favorite change control program to track changes
[client]port=3306
[mysql]default-character-set=latin1
[mysqld]# The TCP/IP Port the MySQL Server will listen onport=3306
#Path to installation directory. All paths are usually resolved relative to this.basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
#Path to the database rootdatadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"
# The default character set that will be used when a new schema or table is# created and no character set is definedcharacter-set-server=latin1
# The default storage engine that will be used when create new tables whendefault-storage-engine=INNODB
http://dev.mysql.com/doc/refman/5.5/en/option-files.html
![Page 10: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/10.jpg)
10
Configuration file hasConfiguration file hassections for the various sections for the various
MySQL ProgramsMySQL Programs
[client]port=3306
[mysql]default-character-set=latin1
[mysqld]# The TCP/IP Port the MySQL Server will listen onport=3306
#Path to installation directory. All paths are usually resolved relative to this.basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
Settings for client programs
General settings
Serversettings
http://dev.mysql.com/doc/refman/5.5/en/server-options.html
![Page 11: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/11.jpg)
11
STOP!!!!!!STOP!!!!!! service mysql stop /etc/init.d/mysql stop
mysqladmin -u root shutdown
http://dev.mysql.com/doc/refman/5.5/en/mysqladmin.html
![Page 12: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/12.jpg)
12
ConnectingConnectingshell> mysql db_name
shell> mysql --user=user_name --password=your_password db_name
mysql db_name < script.sql > output.tab
http://dev.mysql.com/doc/refman/5.5/en/mysql.html
Use the mysql client program to connect to the server
![Page 13: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/13.jpg)
13
Connect to Another HostConnect to Another Host
shell> mysql –host=host db_name shell> mysql -h host db_name
These two commands are equivalent
http://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html
![Page 14: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/14.jpg)
14
ConnectedConnected
![Page 15: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/15.jpg)
15
\s (stats)\s (stats)
http://dev.mysql.com/doc/refman/5.5/en/mysql-commands.html
![Page 16: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/16.jpg)
16
SHOW DATABASESSHOW DATABASESterminate with terminate with ;; or or \g\g
http://dev.mysql.com/doc/refman/5.5/en/mysql-commands.html
; \g
![Page 17: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/17.jpg)
17
\q\qUse \q to exit the mysql client
http://dev.mysql.com/doc/refman/5.5/en/mysql-commands.html
![Page 18: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/18.jpg)
18
Loading DataLoading Data Example databases
World Sakila Download from http://dev.mysql.com/doc/index-other.html
shell> mysql mysql> create database world\gmysql> use world\gmysql> source world_innodb.sql
http://dev.mysql.com/doc/index-other.html
![Page 19: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/19.jpg)
19
Looking At DataLooking At Data mysql> show tables; mysql> select * from City; mysql> SELECT Name, CountryCode --> FROM City → WHERE Population > 10000000;
![Page 20: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/20.jpg)
20
BackupBackup Physical versus logical
Many options, many tools Restoration from backup needs to be tested
shell> mysqldump --all-databases > dump.sql shell> mysqldump --databases db1 db2 db3 >
dump.sql (and to restore)
shell> mysql < dump.sql
http://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html
![Page 21: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/21.jpg)
21
Login/AuthenticationLogin/Authentication MySQL authentication is a little primitive The mysql database has tables for login
information Easy to get confused
Use a tool like MySQL Workbench Be stingy with permissions Read chapter 6 of the MySQL manual
http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html
![Page 22: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/22.jpg)
22
mysql.user tablemysql.user table
http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html
Host User Password Privileges Connection constraints (ssl, time, # connections) The server will first check Host address first, then
user and password. Joe @ foo.net can have separate privs than Joe @
127.0.0.1 and % @ foo.net can trump Joe @ foo.net
![Page 23: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/23.jpg)
23
WorkbenchWorkbenchAdministrative ViewAdministrative View
http://dev.mysql.com/doc/refman/5.5/en/workbench.html
![Page 24: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/24.jpg)
24
You can use the CLI but ...
mysql> select * from user where User='joe' limit 1\G*************************** 1. row *************************** Host: % User: joe Password: Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: NCreate_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: authentication_string: NULL
Yes, you can type in the settings for a new account by hand but it is easy to fat finger one of the thirty privs.
Other tools have similar features and you should use them to avoid dumb errors.
![Page 25: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/25.jpg)
25
Where To Go From HereWhere To Go From Here
Training Classes
Mysql.com/training Local user groups or colleges
Webinars Conferences
MySQL Connect/Oracle Open World MySQL Innovation Day (Webcast) SELF
Planet.MySQL.Com Forums.MySQl.Com
![Page 26: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/26.jpg)
26
SuggestionsSuggestions MySQL
Administrator's Bible – Sheeri Cabral
![Page 27: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/27.jpg)
27
Also SuggestedAlso SuggestedHigh Performance MySQL- Schwartz, Zaitsev, and Tkachenko
3rd Edition!!!
![Page 28: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/28.jpg)
28
A New SeriesA New SeriesEffective MySQL Backup and Recovery, Effective MySQL Optimizing Statements, Ronald Bradford
![Page 29: My SQL 101](https://reader034.vdocuments.us/reader034/viewer/2022052310/5558c3eed8b42a995d8b45e7/html5/thumbnails/29.jpg)
29
MySQL CertificationMySQL CertificationAging but stillthe CertificationGuide