Docstoc

Building, Installing and Configuring MySQL

Document Sample
Building, Installing and Configuring MySQL Powered By Docstoc
					MySQL Essentials Series – Part 1:

Building, Installing and Configuring MySQL
Jimmy Guerrero, Oracle/MySQL
                                      Agenda

                 •     MySQL Overview
                 •     Windows Installer
                 •     Source Distribution Installation (Linux)
                 •     Installation from Dev Tree (Linux)
                 •     Installing from Source (Windows)
                 •     Post Installation tasks
                 •     Next Steps




© 2010 Oracle Corporation
                                                                  2
                            About MySQL



                                   • 14 Years of Development
                                   • 750+ Partners
                                   • 70K+ Downloads Per Day



          Customers across every major operating system, hardware
              vendor, geography, industry, and application type

                Performance ▪ Reliability ▪ Easy to Use


© 2010 Oracle Corporation
                                                                    3
                            MySQL Powers the Web




© 2010 Oracle Corporation
                                                   4
                 MySQL Powers ISV / OEM Products




© 2010 Oracle Corporation
                                                   5
                             Industry Leading Customers




                            Web / Web 2.0                        OEM / ISV's




    SaaS, Hosting, Cloud Computing          Telecommunications          Enterprise 2.0




© 2010 Oracle Corporation
                                                                                         6
                             Popular on Windows
          • 45,000 downloads per day for Windows packages
                  • Server
                  • Tools
                  • Connectors

          • MySQL Survey 2009
                  • 66% percent used Windows for development
                  • 48% deployed on Windows for production
                  • 20% use MySQL & Access
                  • 75% won’t increase use of Access
                  • 60% will increase use of MySQL



© 2010 Oracle Corporation
                                                               7
                            MySQL Windows Installer




© 2010 Oracle Corporation
                                                      8
            MySQL Server Support for Windows


             • All current Windows versions…
                     – Windows XP

                     – Windows Vista

                     – Windows 7

                     – Windows Server 2003

                     – Windows Server 2008

             • Minimal additional requirements

             • 32 and 64 bit x86 architectures supported


© 2010 Oracle Corporation
                                                           9
            Where to download, What to download

        •       http://dev.mysql.com/downloads/mysql/5.1.html
        •       Select “Enterprise” or “Community Server”
        •       Select 32 or 64 bit version




© 2010 Oracle Corporation
                                                                10
            Installation Packages

           • Complete (MSI Installer or unzip)
           • Essentials
                    – Excludes Instance Manager
                    – Documentation
                    – Developer Components.




© 2010 Oracle Corporation
                                                  11
          Select Nearest Mirror and Save File




© 2010 Oracle Corporation
                                                12
            Begin Installation



 • Run mysql-5.1.47-win32.msi




 • Select type of install…

          – Developers select Complete
          – DBAs select Typical




© 2010 Oracle Corporation
                                         13
            Complete Installation




   • Several Next clicks
   • Then Finish
   • Leave Configure checked




© 2010 Oracle Corporation
                                    14
           • Select Detailed Configuration




                  To re-launch configuration wizard:
                  Start->MySQL->MySQL Server 5.1-> MySQL Server Instance Config Wizard



© 2010 Oracle Corporation
                                                                                     15
            Selecting Configuration Options

        • Instance
                 – a running MySQL Server process
                 – or a MySQL Windows Service
        • Instance features and properties are determined by
           • Parameters
           • Startup options
           • Active (enabled) components
        • Configuration file is my.ini
        • Located in the MySQL installation directory



© 2010 Oracle Corporation
                                                               16
           • Select appropriate server type
           • For a trial use Developer Machine




© 2010 Oracle Corporation
                                                 17
           • Perhaps a new concept for some DBAs
           • MySQL supports many database storage engines
           • Can all run at the same time on the same MySQL
             Instance




           • Select Multifunction




© 2010 Oracle Corporation
                                                              18
    • Select location for InnoDB files

    • Performance Tips

    • High performance storage?
             – Specify drive letter
    • Select a disk that has:
             – Space available
             – Where OS is not installed




© 2010 Oracle Corporation
                                           19
                                                  Set expected connections




           • Default Port – 3306

           • Use Strict Mode
        Strict mode controls how MySQL
        handles invalid or missing input values



© 2010 Oracle Corporation
                                                                       20
© 2010 Oracle Corporation
                            21
• Password for MySQL
  “root” account.
        • Equiv to “sa” in MSSQL
        • NOT an OS account
        • Don‟t forget

• Enable access from
  remote if you plan on
  non-local access.




© 2010 Oracle Corporation
                                   22
          • Installed and Started as a service




          • Or from the command line
>"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.1\my.ini“


          • Hint: if you are having any issues append
          –-console

  © 2010 Oracle Corporation
                                                                                                             23
                      Source Distribution Installation




© 2010 Oracle Corporation
                                                         24
                            Installation Overview
          • Download source file
          • Add MySQL user and group
          • Select directory to uncompress
          • Navigate to top-level directory
          • Configure and compile
          • Install the distribution
          • Navigate to installation directory
          • Set permissions
          • Create data directory and initialize GRANT tables
          • Start Server
© 2010 Oracle Corporation
                                                                25
                                   Download Source
                            dev.mysql.com/downloads/mysql/#downloads




© 2010 Oracle Corporation
                                                                       26
                        Create MySQL User and Group

                            > groupadd mysql
                            > useradd –g mysql mysql




© 2010 Oracle Corporation
                                                       27
                            Uncompress Source tar

    > gunzip < mysql-VERSION.tar.gz | tar –xvf –
    > cd mysql-VERSION




© 2010 Oracle Corporation
                                                    28
                        Configure, Compile and Install

                > ./configure –prefix=/usr/local/mysql
                > make
                > make install




© 2010 Oracle Corporation
                                                         29
                            Set Up Option File

    > cp support-files/my-medium.cnf /etc/my.cnf




© 2010 Oracle Corporation
                                                 30
                             Set Permissions

                            > cd usr/local/mysql
                            > chown –R mysql .
                            > chgrp –R mysql .




© 2010 Oracle Corporation
                                                   31
                  Data Directory and GRANT tables

                     > bin/mysql_install_db –user=mysql




© 2010 Oracle Corporation
                                                          32
                            Set Up Option File

                            > chown –R root .
                            > chown –R mysql var




© 2010 Oracle Corporation
                                                   33
                                 Start MySQL Server

                            > bin/mysqld_safe –user=mysql &




© 2010 Oracle Corporation
                                                              34
                   Installing from Development Tree
                                 (Linux)




© 2010 Oracle Corporation
                                                      35
                            Configure the Build Machine
          • Bazzar
          • GNU make
          • autoconf 2.58 or greater
          • automake 1.81
          • libtool 1.5
          • m4
          • bison




© 2010 Oracle Corporation
                                                          36
              Bazzar Repo and Initialize Directory

                  > mkdir mysql-server
                  > bzr init-repo –trees mysql-server




© 2010 Oracle Corporation
                                                        37
                            Create Local Tree

       > cd mysql-server
       > bzr branch lp:mysql-server/5.1 mysql-5.1




© 2010 Oracle Corporation
                                                    38
                            Copy Active Branch

               > bzr branch mysql-5.1 mysql-5.1-build




© 2010 Oracle Corporation
                                                        39
          Prepare Source Tree for Configuration

                            > cd mysql-5.1
                            > autoreconf –force --install




© 2010 Oracle Corporation
                                                            40
                              Configure and Compile

                            > ./configure [OPTIONS]
                            > make
                            > make install




© 2010 Oracle Corporation
                                                      41
                                     Make Install

                            > ./configure [OPTIONS]
                            > make




© 2010 Oracle Corporation
                                                      42
                            Installing from Source
                                  (Windows)




© 2010 Oracle Corporation
                                                     43
                            Configure the Build Machine

          • Bazaar
          • Visual Studio Express
          • Windows Platform SDK
          • CMake
          • Bison for Windows
          • Cygwin
          • WiX




© 2010 Oracle Corporation
                                                          44
                            Building MySQL on Windows

                • Configure the Build Machine
                • Obtain MySQL Source
                • Build the Source – Configure CMake
                • Build the Source – Create VS Solution
                • Build the Source – Build the Solution
                • Test the Build – Run the Test Suite




© 2010 Oracle Corporation
                                                          45
                        Download MySQL Source Files




© 2010 Oracle Corporation
                                                      46
                            Uncompress Download




© 2010 Oracle Corporation
                                                  47
              Build the Source – Configure CMake

           • win/configure.js used to create the
             configuration file (win/configure.data)
             used by CMake
           • Configuration options are defined in
             win/README
           • Example:
                    'cscript win/configure.js WITH_INNOBASE_STORAGE_ENGINE
                       WITH_PARTITION_STORAGE_ENGINE
                       WITH_ARCHIVE_STORAGE_ENGINE
                       WITH_BLACKHOLE_STORAGE_ENGINE
                       WITH_EXAMPLE_STORAGE_ENGINE
                       WITH_FEDERATED_STORAGE_ENGINE __NT__'



© 2010 Oracle Corporation
                                                                             48
                            Execute configure.js




© 2010 Oracle Corporation
                                                   49
           Build the Source – Create VS Solution


          • CMake uses files named CMakeLists.txt
            in each directory to create the necessary
            solution/project files
          • Three batch files are provided to create
            Visual Studio solutions
                  • win/build_vs71.bat
                  • win/build_vs8.bat
                  • win/build_vs8_x64.bat



© 2010 Oracle Corporation
                                                        50
                            Create Visual Studio Solution




© 2010 Oracle Corporation
                                                            51
                        Open Solution in Visual Studio




© 2010 Oracle Corporation
                                                         52
                            Build the Solution


          • Open the solution „MySQL.sln‟ with Visual
            Studio
          • Choose the configuration
                  • Official MySQL builds use Debug and
                    RelWithDebInfo
          • Click Build > Build Solution (F7) to start the
            build




© 2010 Oracle Corporation
                                                             53
                            Select Configuration Options




© 2010 Oracle Corporation
                                                           54
                            Build Solution (F7)




© 2010 Oracle Corporation
                                                  55
                Test the Build – Run the Test Suite


          • Microsoft allows building different
            configurations of the source in the same
            tree
          • Useful for mix-match, release/debug,
            client/server testing
          • Mysql-test-run(mtr) will try to guess
            which configuration but better to be
            specific with –vsconfig dir option



© 2010 Oracle Corporation
                                                       56
                            Test the Build – Debugging


          • Debugging MySQL in Visual Studio can
            be awkward but well worth the effort
          • If you haven’t already done so, build the
            solution’s Debug or RelWithDebInfo
            configuration
          • MySQL Test suite has a ‘-manual-debug’
            option which displays configuration
            options needed to run the server in the
            debugger


© 2010 Oracle Corporation
                                                         57
                            Test the Build – Debugging

          • Example output…
          dir: z:/mysql-5.1-maint_win/mysql-test
          exe: z:/mysql-5.1-maint_win/sql/debug/mysqld.exe
          args: --no-defaults --basedir=z:/mysql-5.1-maint_win/mysql-test --character-sets-
              dir=z:/mysql-5.1-maint_win/sql/share/charsets --secure-file-priv=z:/mysql-5.1-
              maint_win/mysql-test/var --log-bin-trust-function-creators --default-character-
              set=latin1 --language=z:/mysql-5.1-maint_win/sql/share/english --tmpdir=z:/mysql-
              5.1-maint_win/mysql-test/var/tmp --pid-file=z:/mysql-5.1-maint_win/mysql-
              test/var/run/master.pid --port=10510 --socket=z:/mysql-5.1-maint_win/mysql-
              test/var/tmp/master.sock --datadir=z:/mysql-5.1-maint_win/mysql-test/var/master-
              data --log-output=table,file --log=z:/mysql-5.1-maint_win/mysql-
              test/var/log/master.log --log-slow-queries=z:/mysql-5.1-maint_win/mysql-
              test/var/log/master-slow.log --server-id=1 --loose-
              innodb_data_file_path=ibdata1:10M:autoextend --local-infile --loose-skip-innodb --
              loose-skip-ndbcluster --key_buffer_size=1M --sort_buffer=256K --
              max_heap_table_size=1M --ssl-ca=z:/mysql-5.1-maint_win/mysql-
              test/std_data/cacert.pem --ssl-cert=z:/mysql-5.1-maint_win/mysql-
              test/std_data/server-cert.pem --ssl-key=z:/mysql-5.1-maint_win/mysql-
              test/std_data/server-key.pem --gdb --skip-log-bin --core-file --open-files-limit=1024




© 2010 Oracle Corporation
                                                                                                      58
                            Post Installation Tasks




© 2010 Oracle Corporation
                                                      59
                            Post Installation Tasks


        • Delete the test database
        • Secure the root account
        • Ensure root cannot login from an anonymous
          host
        • Delete anonymous accounts

        • MSI installer or mysql_secure_installation
          script (in scripts directory) can be used to
          automate some of these steps


© 2010 Oracle Corporation
                                                         60
                            Delete the test Database




© 2010 Oracle Corporation
                                                       61
                            root Accounts


    •       One (or two) accounts are created named root
    •       Super user accounts that can do anything
    •       The initial root account passwords are blank
    •       One root account for connecting from local host
    •       Other allows connections from any host
    •       Rename the root account with a different (more
            difficult name) to prevent brute-force attacks




© 2010 Oracle Corporation
                                                          62
                            Secure root Account




© 2010 Oracle Corporation
                                                  63
                        Change the Name of root User




© 2010 Oracle Corporation
                                                       64
                              Anonymous Accounts

        • Two anonymous user accounts are created
          by default
                •       Empty user names and no passwords
                •       One anonymous account for connections from
                        the local host
                •       Prior to 5.1.16 had global privileges, just like the
                        root accounts
                •       Other is for connections from any host
                •       Has all privileges for the test database or other
                        databases with names that start with test
        • Delete these accounts!



© 2010 Oracle Corporation
                                                                               65
                            Delete Anonymous Accounts




© 2010 Oracle Corporation
                                                        66
                                Directories

        • Database and most log and status files are
          stored in the data directory by default
        • Default directory specified in config file or at
          startup
        • By default
                • C:/Documents and Settings/All Users/Application
                  Data/MySQL/MySQL Server 5.1/Data/
        • Confirm location with:
                • SHOW VARIABLES like „data%‟;
        • Remember: data files are portable so lock
          them down

© 2010 Oracle Corporation
                                                                    67
                            Storage Engine Directories
        • MyISAM
                 • table.frm (structure)
                 • table.MYI (indexes)
                 • table.myd (data)
        • InnoDB
                 • table.frm in data directory
                 • Data and indexes stored in tablespace made up of datafiles




© 2010 Oracle Corporation
                                                                                68
                            Next Steps




© 2010 Oracle Corporation
                                         69
                            MySQL Enterprise
                            • MySQL Enterprise Server
                            • Monthly Rapid Updates

      Server                • Quarterly Service Packs

                            • Hot Fix Program
                                              • Subscription:
                            • Extended End-of-Life

                                                    •    MySQL Enterprise
                                •
                                                    • License (OEM):
                                    Global Monitoring of All Servers
                                •                        Embedded Server
                                    Web-Based Central •Console
       Monitor                  •   Built-in Advisors
                                                       • Support
                                •   Expert Advice
                                •
                                                    • MySQL
                                    Specialized Scale-Out Help Cluster
                                                      Carrier-Grade
                            •                       • Training
                                    24 x 7 x 365 Production Support
                            •       Web-Based Knowledge Base
                                                    • Consulting
       Support              •       Consultative Help
                                                    • NRE
                            •       Bug Escalation Program



© 2010 Oracle Corporation
                                                                            70
            MySQL Enterprise Monitor w/ Query Analyzer
   • Single, consolidated view into entire MySQL
     environment
   • Auto discovery of MySQL Servers, Replication
     Topologies
   • MySQL Query Analyzer with Visual Correlation Graphs
   • Customizable rules-based monitoring and alerts
   • Identifies problems before they occur
   • Reduces risk of downtime
   • Makes it easier
     to scale-out without
     requiring more DBAs


© 2010 Oracle Corporation
                                                         71
                            Connectors & Visual Studio
        • ODBC, JDBC, C++, C….
        • Connector/NET
                 • C#
                 • ASP.NET
                 • VB.net
        • Connector/Net
                 •    Implements the ADO.NET interfaces
                 •    Integrates into ADO.NET aware tools
                 •    Fully managed ADO.NET driver
                 •    100% pure C#
        • Integrates with Visual Studio
                            http://www.mysql.com/products/connector

© 2010 Oracle Corporation
                                                                      72
                              MySQL Workbench
   •       ER Modeling
           •      Design
           •      Forward/Reverse Engineering
           •      Schema Synchronization
           •      Documentation
   •       New! Administrator Plugin
           •      Start/Stop Server
           •      Account/User Management
           •      Server Variables
           •      Server Logs
           •      Database Objects

                 http://wb.mysql.com/



© 2010 Oracle Corporation
                                                73
          ISV / OEM Resources
      • “Running MySQL Embedded Server on Windows
        Securely” live webcast
               • 10 June, 9 am Pacific
               • http://mysql.com/news-and-events/web-seminars/display-
                 546.html
      • White Papers, Webinars, Case Studies
               •      http://mysql.com/why-mysql/isv-oem-corner/resources.html
      • Webinar Replays
               • http://mysql.com/news-and-events/on-demand-webinars/
      • Questions?
               • http://www.mysql.com/about/contact/sales.html?s=oem
               • Phone: USA=+1-866-221-0634; Outside USA = +1-208-327-
                 6494



© 2010 Oracle Corporation
                                                                                 74
                            MySQL on Windows Resources
                                   Product Downloads
                                       Whitepapers
                                      Visual Guides
                                        How-Tos
                                   Upcoming Webinars
                                    Developer Articles
                                  Visual Studio Articles
                               Archived Webinar Playbacks
                                 Archived Webinar Slides
                                       User Forums

                      http://www.mysql.com/windows/
© 2010 Oracle Corporation
                                                            75