MySql 8 Setup


Install MYSQL on OEL Linux 7.6
  • Download the Mysql Yum Repository
  • Install the repository
    • rpm install mysql157-community-el7-3.noarch.rpm
  • Install the MySQL package
    • yum install mysql-community-server -y
  • Enable and Start MySQL at startup
    • systemctl enable mysqld
    • systemctl start mysqld
  • Make sure SElinux is running in permissive mode
    • # setenforce Permissive
  • Make the Permissive setting permanent, edit the "/etc/selinux/config"
    • SELINUX=permissive

  • If installing the mySQL data directory onto a different location do the following
    • mkdir /u01
    • mkdir -p /u01/data
    • mkdir -p /u01/log_bin
    • mkdir -p /u01/tmpdir
    • chown -R mysql:mysql /u01
    • chmod -R 755 u01
    • chmod 700 /u01/data ( not doing this will cause permission error when trying to start mysql )
    • Edit the /etc/my.cnf file and add the following
      • user=mysql
      • datadir=/u01/data
      • log_bin=/u01/log_bin/myDB
      • tmpdir=/u01/tmpdir
  • Run the /usr/bin/mysql_secure_installation script
    • when being prompted for the root password (  #grep 'temporary password' /var/log/mysqld.log )
  • Log in to MySQL
    • mysql -u root -p “password "
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 23
    Server version: 5.1.67 Source distribution

    Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql>
  • Create a new Database
    • mysql> create database mydatabase;
    Query OK, 1 row affected (0.01 sec)

    mysql>
    mysql> show databases;
    +——————————+
    | Database                      |
    +——————————+
    | information_schema     |
    | mydatabase                  |
    | mysql                            |
    +——————————-+
    3 rows in set (0.00 sec)

    mysql>
    mysql> use mydatabase;
    Database changed
    mysql>

Install MySQL workbench

Install MySQL test database for load generation

  • #mkdir /mysqlslap_tutorial
  • #cd /mysqlslap_tutorial
  • #bzip2 -dfv employees_db-full-1.0.6.tar.bz2
  • #tar xvf employees_db-full-1.0.6.tar
  • edit the employees.sql file and make the following change
    • set storage_engine = InnoDB; ==> set default_storage_engine = InnoDB;
    • select CONCAT('storage engine: ', @@storage_engine) as INFO; ==> select CONCAT('storage engine: ', @@default_storage_engine) as INFO;
  • #mysql -h localhost -u root -p
    • create user sysadmin identified by 'mypassword';
    • grant all on *.* to sysadmin;
  • #mysql -h localhost -u sysadmin -p -t < employees.sql
  • Once this has completed and the database created you can start testing load
  • Test  with 50 concurrent connections with auto generated queries run 10 times
    • mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=10 --auto-generate-sql --verbose
    Benchmark
            Average number of seconds to run all queries: 0.197 seconds
            Minimum number of seconds to run all queries: 0.168 seconds
            Maximum number of seconds to run all queries: 0.399 seconds
            Number of clients running queries: 50
            Average number of queries per client: 0
  • play around and create the concurrent session and run times ie run 100 connections and 100 times
    • mysqlslap --user=sysadmin --password --host=localhost  —concurrency=100 --iterations=100 --auto-generate-sql --verbose

Comments

Post a Comment

Popular posts from this blog

SQL Server container with Pure Storage Docker volumes

Image
SQL Server on Docker with Pure Storage Persistent Volumes Docker and containers have become an integral part of virtualisation. Having the ability to spin up containers has almost instantly transformed the way we build, develop and manage applications. Many of us have been using some sort of virtualisation for decades as this is not a new concept. Due to their speed, simplicity and automation, container usage has garnered substantial popularity and are becoming ubiquitous in the field of virtualisations. So why use containers ? Decouple application runtime from infrastructure. Runs instantly. Process and user isolation for application multi-tenancy. Key building blocks for microservices. But what happens when you delete the container ? The main issue in deleting a container is that you delete the writeable layer and any data that was there, hence no data persistence. Docker volumes provide us a way to decouple your application from its state, to the point where you c...
Read more

Oracle 19c Database clones using the Pure Storage Docker plugin

Image
  Docker and containers have become a integral part of virtualization, being able to spin up containers almost instantly have transformed the way be build, develop and manage applications. In this blog I will show you how we can rapidly create Oracle 19c container clones using the Pure Storage Docker plugin with persistent volumes and the new Oracle 19c container image on the Oracle Container Registry https://container-registry.oracle.com/pls/apex/f?p=113:4:2886566670416#     Installing the Pure Plugin ( Latest version is 3.10 ) 12:24:30 root@docker ~ # docker plugin install purestorage/docker-plugin:v3.10 --alias pure --grant-all-permissions Enter the Array Configuration in the /etc/pure-docker-plugin/pure.json file  {      "FlashArrays":[          {              "MgmtEndPoint":“192.168.111.130",              "APIToken":" 476d58bc-3c91-0d10-1b9e-0f31058c462...
Read more

Cloning SQL Server Containers With Pure Storage Docker Volumes

Image
In my first blog  https://bannaych.blogspot.com/2020/04/mssql-server-on-docker-with-pure.html  I showed you how we can create SQL Server containers using the Pure Storage Docker plugin. In this blog I will show you how we can leverage the Pure Storage Docker plugin to instantly create a clone volume from the source volume, then use Pure Storage crash consistent snapshots to refresh the clone volume. Crash consistent snapshots provide us with a point in time copy of the volume/database, because we use metadata pointers to the original blocks on disk, the snapshots are created instantly with no initial space consumption, then those snapshots can be used to refresh volumes or create new ones. The big advantage of using snapshots is that we no longer need to backup and restore large databases to provision and refresh TEST/DEV/UAT environments. In the world of DevOps being agile is imperative and Pure Storage space efficient snapshots are key to maximising productivity of database ...
Read more