Not signed in (Sign In)

MySQL: Installing MySQL

Bottom of Page

1 to 1 of 1

  1.  
  1.  
    Administrator
    • CommentAuthoradmin
    • CommentTimeSep 23rd 2008 edited
     

    MySQL

    Installing MySQL

    • tar xzvf mysql-4.1.13a.tar.gz
    • cd mysql-4.1.13a
    • ./configure \
      --prefix=/usr/mysql \
      --with-mysqlfs --with-isam \
      --with-mysqld-user=mysql \
      --with-example-storage-engine \
      --with-archive-storage-engine \
      --with-csv-storage-engine \
      --enable-thread-safe-client \
      --enable-local-infile
    • make
    • make install


    Create mysql user and group

    • groupadd mysql
    • useradd -g mysql mysql


    Create temporary and data directory

    • mkdir /usr/mysql41/tmp
    • mkdir /usr/mysql41/var


    Initiate database

    • /usr/mysql41/bin/mysql_install_db


    Setting permissions

    • chown -R mysql /usr/mysql41/var
    • chgrp -R mysql /usr/mysql41
    • chown -R mysql /usr/mysql41/tmp


    Copy default config file

    • cd support-files
    • cp my-medium.cnf /usr/mysql41/var/my.cnf


    Edit the configuration file and change the socket path for both client and server section.

    • vi /usr/mysql41/var/my.cnf


    Setting init script

    • cp mysql.server /etc/init.d/mysql41
    • chmod +x /etc/init.d/mysql41
    • chkconfig --add mysql41


    Set the path of my.cnf file in init script

    • vi /etc/init.d/mysql41
      conf=/usr/mysql41/var/my.cnf


    Start Mysql

    • service mysql41 start



    How to take full mysql backup using mysql dump


    mysqldump --all-databases -u root -p > mysql.sql

    Important: In Mysql 5 you may get this error "Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES". In this case use below command for mysql 5.

    mysqldump --single-transaction --all-databases -u root -p > mysql.sql

    IF you need to restore that backup to an old version of mysql then use below switch

    --compatible=mysql40
    or
    --compatible=mysql323

    Extracting a specific database backup from a large mysql dump file


    Suppose you have a 100 GB mysql dump file mysql.sql.
    First execute this command to get the line numbers of all "CREATE DATABASE " statements.

    grep -n "CREATE DATABASE" mysql.sql

    Output will be like...
    113968:CREATE DATABASE /*!32312 IF NOT EXISTS*/ `MyDatabase1`;
    209784:CREATE DATABASE /*!32312 IF NOT EXISTS*/ `MyDatabase2`;
    904563::CREATE DATABASE /*!32312 IF NOT EXISTS*/ `MyDatabase3`;

    We want to extract backup of MyDatabase2, It starts from line 209784 and goes up to line 904563. We can get that data by using below command.

    csplit -k mysql.sql 209784 904563

    This command will create 3 files with names like XX00,XX01,XX02. XX00 will have data from line 1 to 209784, XX02 will have data from line 209784 to 904563. And XX03 will have data from line 904563 to the end of file.

    So XX01 is the backup we require and it can be restored with mysql command line.

    Reset forgotten mysql root password (DONT DO IT IF you have PLESK installed)

    -Stop the mysql
    -Now start it with --skip-grant-tables

    /usr/bin/mysqld_safe --skip-grant-tables &

    -Now ...

    mysql --user=root mysql
    Press enter when it prompt for password. And after login run this ..

    update user set Password=PASSWORD('new-password-here') WHERE User='root';

    -Kill mysql and start it again with your usual start script (e.g /etc/init.d/mysql start).

1 to 1 of 1

  1.