To create a new user within the MySQL shell use this sql syntax:

At this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell.

Therefore, the first thing to do is to provide the user with access to the information they will need.

The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

Your changes will now be in effect.

How To Grant Different User Permissions

Here is a short list of other common possible permissions that users can enjoy.

  • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
  • CREATE- allows them to create new tables or databases
  • DROP- allows them to them to delete tables or databases
  • DELETE- allows them to delete rows from tables
  • INSERT- allows them to insert rows into tables
  • SELECT- allows them to use the Select command to read through databases
  • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users’ privileges

To provide a specific user with a permission, you can use this framework:

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name.

Each time you update or change a permission be sure to use the Flush Privileges command.

If you need to revoke a permission, the structure is almost identical to granting it:

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:

To test out your new user, log out by typing

and log back in with this command in terminal:

MySQL 5.7 General Availability (GA) has been released for quite few months Download now and we have not have the chance to take a look over new added features and capabilities. This version looks very exciting and the world’s most popular open source database improved usability, manageability, and security.

 Here are key enhancements included in MySLQ 5.7

Performance & Scalability

Improved InnoDB scalability and performance on temporary tables, faster online and bulk load operations.

JSON Support

   With the newly added JSON support in MySQL, you can now combine the flexibility of NoSQL with the strength of a relational database.

Replication improvements

for increased availability and performance. They include multi-source replication, multi-threaded slave enhancements, online GTIDs, and enhanced semi-sync replication.

Performance Schema

delivering much better insights. We’ve added numerous new monitoring capabilities, reduced the footprint and overhead, and significantly improved ease of use with the new SYS Schema.

Security

We are fulfilling “secure by default” requirements and many new MySQL 5.7 features will help users keep their database secure.

Optimizer

We have rewritten large parts of the parser, optimizer, and cost model. This has improved maintainability, extend-ability, and performance.

GIS

Completely new in MySQL 5.7 and including InnoDB spatial indexes, use of Boost.Geometry, along with increased completeness and standard compliance.

 

percona xtrabackup

In the previous article we saw how we could install and make use of the Percona Xtrabackup backup utility for MySQL.

Here is a script that will create a full database backup of you Innodb engine schemas(tables) without locking any of them and with no down time.

Is important to know that the script has 2 steps, one will create the initial backup piece and the second step will consolidate the initial backup by applying the log so it become “RESTORE READY” or in other words consistent.

So in case you loose the database recovery using this type of backup will not need any logs to be applied to it.

 

Here is the Backup script

  • use it as template and alter the variables as per your needs

  

Now let’s create the Restore Script

Very important:

  • before you plan to restore you backup using xtrabackup utility make sure you do the following
  • 1 – Stop you MySQL instance
  • 2 – Remove all files located in your  “datadir”  MySQL variable(is where your data files are locates)
  • 3 – run the Restore command(will copy all the backup to the locations stated in the my.cnf file)
  • 4 – start you MySQL instance

  

I hope this was helpful.

Fell free to drop a comment with your opinion.

 

Just got this error while adding a new MySQL instance on my DEV server.

Cannot open table mysql.innodb_index_stats

Inside the error log:

InnoDB: Error: Table “mysql”.”innodb_table_stats” not found

  • I have no idea what started this but after doing some research i ended up finding that is bug and there is a solution for it.

Here are the steps i have followed and solved my problem:

 

1- You need to drop these tables from mysql schema:

 

We all know that these days we have all types of GUI tools that can help us export data out of MySQL in various formats including csv format(comma separated values).  But in some cases we need to incorporate a data export via shell scripts/sql that are part of a home cooked ETL process(also this is done using less resources).

 What is the basic syntax for use to do a data export from MySQL to a csv external file.

  • you can implement various business logic in your extract sql statement.

Note:

-the user who will do the export must have the FILE grant.

 

The output file should look something like this:

To see how to import the csv file into MySQL follow this Article.

We all know that these days we have all types of GUI tools that can help us import data into MySQL from various formats including csv format(comma separated values).  But in some cases we need to incorporate a data import via shell/sql scripts that are part of a home cooked ETL process(also this is done using less resources).

 What is the basic syntax for use to do a data import from a csv file into a MySQL table.

Note:

-the user who will do the export must have the FILE grant.

 

Example of importing data from csv file in MySQL

A can say that is quite fast for an open source database.

To see how to export the csv file into MySQL follow this Article.

percona xtrabackup

 

mysqldump is the common backup tool for MySQL but sometimes depending on you database this backup option is not going to do the job for you as performance will drop.

So what are our option when looking for a better backup tool for our MySQL database ?

The most noticeable difference between this two is the fact that Xtrabackup is FREE.

 

Percona XtraBackup is an open-source hot backup utility for MySQL – based servers that doesn’t lock your database during the backup.

Lets’s list some of  Xtrabackup  capabilities

  • Create hot InnoDB backups without pausing your database
  • Make incremental backups of MySQL
  • Stream compressed MySQL backups to another server
  • Move tables between MySQL servers online
  • Create new MySQL replication slaves easily
  • Backup MySQL without adding load to the server

So now let’s go to work and see how can we download and install Percona’s Xtrabackup.

  • Install the yum repository on your host.(this will resolve you requirements much better)

  • Query the Percona Yum Repo for the Xtrabackup available

  •   Install the Xtrabackup for your type of Operational System arch

 

  • Now with Percona Xtrabackup installed find where the was installed in the /usr/bin

 

  • Also see where the innobackupex was installed in the /usr/bin

 

Great now the Percona Xtrabackup tools was install successfully.

In future articles we will see how we can create backups and recover using  Percona Xtrabackup tool.

You will see in this article how to use the option file in MySQL to avoid using clear text password in our daily maintenance tasks.

   The first thing you need know is that the default options are generally read from the following files in the given order(unless you specify a new option file location at MySQL start time):

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. my.cnf in the DEFAULT_SYSCONFDIR specified during the compilation
  4. my.cnf in the path, specified in the environment variable MYSQL_HOME (if any)
  5. the file specified in --defaults-extra-file (if any)
  6. user-home-dir/.my.cnf

So in our case we are going to use the 6th option where we will create the .my.cnf file and define all the option groups to satisfy our needs(log in with no password for example).

In MySQL the option file or the configuration file(my.cnf) comes with some predefined groups.

 

What is a group in MySQL option file 

-group is the name of the program or group for which you want to set options. After a group line, any option-setting lines apply to the named group until the end of the option file or another group line is given. Is good to know that option group names are not case sensitive.

 

Example of ~/.my.cnf file

  • i normally use this option file ~/.my.cnf only with this options(password) to make my life easier and also avoid passing clear text password in my crontab MySQL jobs.

 

Very important :

-make sure that you have restricted access to this file, normally i only allow the owner to read and write on it.

 

In this article we will see how we can setup the Percona Server YUM repository. The YUM repository will make it easy for you to install and manage your Percona Server software.

As root user run the command bellow: 

 

To verify the the repository is in use use the command bellow:

Great now you have access to Percona Software Repository.

Happy installing !

 

 

 

install mysql

What is MySQL ? 

MySQL is an open source database management software that helps users store, organize, and retrieve data. It is a very powerful program with a lot of flexibility—this tutorial will provide the simplest introduction to MySQL

Now let`s go ahead and see how we can install MySQL on a Linux machine.

 Download MySQL rpm files from Oracle website or from MySQL Website.

 We are going to use the MySQL 5.6.23 for Linux 64 bits in this installation example.

  • During the time the installer runs a password will be generated for you and will be stored inside the .mysql_secret file located in roots home directory.

  • Also a my.cnf file will be created for you by the installer and is located at /usr/my.cnf.

-the my.cnf file is responsible for holding all your database configuration variables.

Here is the content of the sample my.cnf file, you can alter as you wish. Also there is a link inside the file pointing to MySQL website page that gives more details about configuration parameter.

  • Now that we have our root password and we know where our my.cnf file is located let’s start our MySQL service and log into our MySQL database 

Start MySQL service

Log into the MySQL database using the password inside the .mysql_secret

Next we need to alter the root password to fit our needs.

-for this example i will use the password ‘root‘.

Test the password 


Great our MySQL database is up and running. This was a basic MySQL database installation using the default variables for installation, we will look into customized MySQL in future articles.

mysql replace string

Sometimes i need to find and replace some strings in a column in MySQL so for this task MySQL uses the replace() function.

 This is a string function and the base syntax is as follows.

  • you can pass a string to it or use the entry provided by a column.

 

Example of using the replace() function in MySQL

1 – Simple string replacement:

 

2 – String replacement using the where clause:

 

Note:

-the function is case sensitive, so be careful when using. As you can see bellow nothing was changes.

cleanup logs

What is up will all this binary-log in my MySQL database ? They are taking to much space and i need ot get rid of them !
Not so fast ! First you need to know what are the binary logs and for what they are used:
What are they ?
The binary log contains “events” that describe database changes such as table creation operations or changes to table data.

What do they serve for ?
Replication – the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.
Recovery Operations -require the use of the binary logs. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

Cons form enabling binary log in MySQL
Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.
So if you are on production MySQL database binary logs are pretty important.

There is allot to talk about the binary logs but now let’s see how can we safely clean old logs.

We will start by listing our binary logs:

You can choose to cleanup until one binary log only by passing the name if it:
This will erase all binary logs before bih001.000075, it will not cleanup the actual binary log that you have passed.

Or you can use a timestamp predicate type:

Another way is to setup a log purge policy.
This is possible by setting up the variable expire-logs-days. This variable will setup the value of days that the log will automatically expire.

Example:

  • is important to know that if you have Replication enabled you need to consider the lag time that you have between your Master > Slaves.
  • also make sure you have your backups up to date before you start cleaning up binary logs.

Some times when you build a MySQL server without going thrum the small details and you just
wanna put it online as fast as possible the long run is affected as you will end up doing unwanted emergent maintenance or your MySQL instance will become unavailable.
Because of the reasons stated in the previous paragraph the “Can’t open file: ‘./*.frm’ (errno: 24)” error will appear.

  • Why do i get this error ?
  • Well this a common error when you install everything using the default parameters.
    The “Can’t open file: ‘./*.frm’ (errno: 24)” error comes up when your operational system(host) reached the
    max open files value available for the user who is running the MySQL service.

  • How to find the max open files value for my MySQL user ?
  • You need to log in as the MySQL user and issue the following command:

  • How can we alter the value of the max open files value for the MySQL user ?
  • For this you need to log in as the root user and edit the “/etc/security/limits.conf” file.
    Here is how i do it for my MySQL in order for him to be able to open 25.000 files.

  • Where :
  • nofile – max number of open files

    hard – for enforcing hard limits

  • In order for the MySQL instance to be able to apply the new parameter value you need to restart your MySQL instance.
  • After restart check you open_files_limit value using the syntax bellow. Also i good to know that your MySQL instance will
    apply the host parameter values if you don’t specify it on your my.cnf MySQL parameter file.

  • If you want to make this parameter available to your my.cnf file just add the values bellow to your my.cnf file.
  • Before doing this you should check the value on the host to make sure that your MySQL parameter won’t pass the host one.

    I hope this was helpfull.

    Cool and easy script to get the MySQL users definition for a specific host.
    This can be altered as you wish.
    This is useful when you are altering users definition in batch mode

  • Here is an example of it
  • Here is a cool script that will drop all of your MySQL databases, all but mysql and informatoion_schema databases.
    I use this script when ever i want to rebuild my replication server.

     

    The command will create the drop database statement for all the database but mysql and information_schema databases then pipe the
    command into mysql to execute.

    The output would be some thing like this:

    If you are using referential integrity on MySQL, you can easily run into this error with the InnoDB engine.
    It frequently seems to occur with an ALTER TABLE statement. It can mean many things but typically it means the data types don? match between the foreign key and primary key column. Likewise, it can mean one of the column data types disagrees in a multiple-column foreign to multiple-column primary key constraint.

    The error by itself isn’t very handy. This is a sample:

    You can try SHOW WARNINGS but you will discover more about the error by running the following command as the root superuser:

    It returns a dump of the InnoDB’s activity. You can see it by unfolding the complete log, if you are interested in the details.

    The significant part of the log to solve this type of problem is:

    While this is more information, it isn’t necessarily enough to solve the problem when you are new to MySQL.
    The first place you should look is whether the data types agree between the foreign key and primary key columns.

    The most common variation that I’ve run into is where the primary key column uses a int unsigned data type and the foreign key column uses an int data type. It’s quite nice that the InnoDB Engine stops this cold. Naturally, you fix it by changing the foreign key data type to match the int unsigned data type.

    How can we see all the routines inside our Mysql database.
    In Mysql functions, procedures can are called sorted routines. To query the information of such you can either use the SHOW command:

    Or you can use the syntax ot get the definition for a single routine:

    Another way for you to list the rotines is to query the INFORMATION_SCHEMA.ROUTINES table
    You will get more info on the rotine you want, such as it’s type and it’s definition.

    MySql Free tutorials

    Changing the Mysql database character set is needed sometimes so that our data wont appear scrambled on our website, to do so use this syntax.

  • Syntax to change the database character set for Mysql.
  • What are fragmented tables?

    If there are random insertions into or deletions from the indexes of a table, the indexes may become fragmented.
    Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages or that there are many unused pages in the 64-page blocks that were allocated to the index.
    The symptoms of fragmented tables can be that table can take more disk space than needed or the results may return slower with more disk I/O than needed. INNODB users need to check the fragmentation often because when INNODB marks data as deleted, it never overwrites the blocks with new data. It just marks them as unusable. As a result, the data size is artificially inflated and data retrieval is slowed.

  • Now create a routine running the script below.
  • Is good to remember that when de-fragmenting a table, it will lock the table, so make sure you can afford the lock.

    Point-in-time recovery refers to recovery of data changes made since a given point in time.
    Point-in-time principles:

  • the server must be started with the “–log-bin” option to enable binary logging
  • To restore data from the binary log, you must know the name and location of the current binary log files.
  • To see a listing of all binary log files, use this statement:

  • Find where the lob-bins are located on your host. I do this by looking into your my.cnf file(located in /etc/my.cnf normally).
  • To get the content of the log you need to use the mysqlbinlog tool that comes with mysql installation.
  • You can alter the output and use it as you wish to rollback or restore

        What is the most important task you need to do in regards to your application ? I making sure you have a backup of it !

    In this post we will show how to create script that will generate a zipped backup of each of our MySQL schema.

    1- Enable password-less log in from localhost

    Create the file “.my.cnf” in you home folder and alter the permission on it as chmod 600

     

    2- Create the script “zippedbkp” that will create individual zipped backups of your databases.

    Make sure you alter the permission on it as “chmod 700 zippedbkp“.

    You will need to create the directories where the backup will be held and make sure the used user to run the backup will have
    read & write rights on it.

    3- Create a crontab routine to automatize your backup.

    And that is it, just make sure you also create cleanup routine.

    Also view this video tutorial to learn how to use the MySqlDump

    To improve the response from your MySQL server you need to add following two configurations to your MySQL server:

  • 1- query_cache_size

  • The amount of memory (SIZE) allocated for caching query results. The default value is 0, which disables the query cache.

  • 2- query_cache_type

  • Set the query cache type.
    It has as options :
    0 : Don’t cache results in or retrieve results from the query cache.
    1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
    2 : Cache results only for queries that begin with SELECT SQL_CACHE

    To enable this parameters you need to be the administrator of the server.

    Steps to follow :

  • 1-Connect as root user
  • 2-Set the query_cache_size size:(set to 64mb)
  • 3-Alter you my.cnf file and add the following:
  • Where :
    query_cache_size – This is the size of the cache in bytes. Setting this value to 0 will effectively disable caching.
    query_cache_type – This value must be ON or 1 for query caching to be enabled by default.
    query_cache_limit – This is the maximum size query (in bytes) that will be cached.

    Next restart your MySQL server so that your changes become permanent.

    MySql Replication

    MySQL is very easy to setup and requires minimum of time and resources.
    In this tutorial we will setup a simple Master -> Slave replication.

    In this example we will use the following servers bih001(10.000.1.189)/Master and bih002(10.000.1.210)/Slave.

    Follow the steps that are required to configure the MySQL replication environments.

    • Step 1: Edit the configuration files & start the MySQL Servers.

    Add the following lines to your Master server in our case bih001

    When editing the configuration file /etc/mysql/my.cnf and be sure to use different server ids for the hosts

    • Step 2: Add the following lines to your Slave server in our case bih002.

    Make sure the paths are correct.

    • Step 3:Start your MySQL servers with the new configuration

    • Step 4:Create Replication User

    Create an account on the master server that the slave server can use to connect.

    This account must be given the REPLICATION SLAVE privilege.

    • Step 5:Find the master_log_file & master_log_pos

    Now you need to find the master_log_file & master_log_pos that will be used for the Initialize Replication command .

    • Step 5:Initialize Replication

    Connect to the SLAVE server and initiate replication using the following script.

    Where:
    MASTER_HOST: the IP or host-name of the master server, in this example blue or 10.000.1.189.
    MASTER_USER: this is the user we granted the REPLICATION SLAVE privilege to in Step 4.
    MASTER_PASSWORD: this is the password we assigned to REPLICATION user in Step 4
    MASTER_LOG_FILE: is mysql-bin.000004 (wouldn’t be empty if there were existing writes to be picked up from the master)
    MASTER_LOG_POS: is 383(would likely be different if there were existing writes to be picked up from the master)

    • Step 6:Start replication process

    Connect to the slave server and issue the following command:

    To check the status of the replication you can use the commands:

    Also create objects on the master and check if they are created on to the slave server as well.

    The syntax to alter a user password is :

    Example :

    or

    or it can be done like this as well by using the GRANT option

    Note:

    Very important to know that SET PASSWORD may be recorded in server logs or in a history file such as ~/.mysql_history, which means that clear-text passwords may be read by anyone having read access to that information. So check the permissions on this file.

    This program enables you to improve the security of your MySQL installation

  • 1- Start the MySQL service.
  • 2- Use mysql_secure_installation to improve the security of your MySQL installation
  • When executed, the script prompts you to determine which actions to perform.

  • 1- Make a full backup of your database
  • 2- Shutdown your mysql server.
  • 3- Create/Choose the new location where the file will be written and make sure is owned by the mysql user and mysql group. In our case the new location will be
    “/mysql_log”
  • 4- Edit you my.cnf file and add to [mysqld] the new “log_slow_queries” path value.
  • 5- Relocate general_log_file.log file to the new locations keeping the original file permissions. This only if the general_log_file.log was enabeled already. If it does not exixsts
    then create it before you start the server.
  • 6- Start your server now and enable the .
  • Configuring/change MySQL bin-logs files location

    Many of you maybe have faced the problem with growing size of their database and desperate need of storage on the partition where MySQL database is located.

  • 1- Make a full backup of your database
  • 2- Shutdown your mysql server.
  • 3- Create/Choose the new location where the file will be written and make sure is owned by the mysql user and mysql group. In our case the new location will be
    “/home/mysql_dados/xxxx2_bin_logs/”
  • 4- Edit you my.cnf file and add under [mysqld] the new “log-bin” path value.
  • 5- Relocate all bin-log files including the index file to the new locations keeping the original file permissions.
  • 6- Start your server now.
  • InnoDB is a database engine(compatible with ACID) transactions with insurance for MySQL that has commit,
    rollback and recovery capabilities to protect the User data.

  • 1 – First stop your database
  • 2- Changes the my.cnf file by adding the following lines,
    where innodb_data_home_dir should receive the specific path for your system.
  • 3- Copy the ibdata1 file to the new location.
  • 4- Start the Database .
  • 5- Log in the database and check the value of the parameter .
  • Changing the InnoDB location is done.

    To enable MySQL binary log you need to alter the mysql configuration file “my.cnf“. The file os located on linux systems at /etc/my.cnf.

    So just add the following line to you conf file. “log-bin=mysql-bin” under the [mysqld] section:

    The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows). The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes: replication and recovery.

    MySql Free tutorials

    Here is the list of articles on how to install, implement and make use of this great database called MySQL.

    MySQL is the world’s most popular open source database.

    None of the tutorials Represent Following Oracle Corporation in any way, and is not advised to be used in production environments before testing. So aodba.com will not be held Responsible for any damages that will produce the materials presented to any database environments. Use with care.
    mysql