How to set initial value and auto increment in MySQL ?

How to set initial value and auto increment in MySQL ?

we can use below command -

ALTER TABLE employee AUTO_INCREMENT=1001;

or if you haven't already added an id column, also add it

ALTER TABLE employee ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD INDEX (id);

Example :-

MySQL - Setup an auto-incrementing primary key that starts at 20001:

Step 1.  create your table:

create table employee(

  id       int(11) auto_increment, 

  name varchar(40),

  PRIMARY KEY (id)

)

Step 2 set the start number for auto increment primary key:

ALTER TABLE employee AUTO_INCREMENT=20001;

Step 3 insert some rows:-

insert into employee (name) values("pappu");

insert into employee(name) values("mamu");

Step 4, interpret the output:

select * from employee

'20001', 'pappu'

'20002', 'mamu'

If you need to add column for auto increment

alter table employee add column id int(5) NOT NULL AUTO_INCREMENT FIRST

This query for add column at first. Now you have to reset auto increment initial value. So use this query 

alter table employee AUTO_INCREMENT=20001

Read more...

How to change the default charset of a MySQL table ?

How to change the default charset of a MySQL table ?

If you want to change the table default character set and all character columns to a new character set, use a statement like this:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

So query will be:

ALTER TABLE employee CONVERT TO CHARACTER SET utf8;

Read more...

How to reset AUTO_INCREMENT in MySQL ?

How to reset AUTO_INCREMENT in MySQL ?

How can we reset the auto-increment of a field in Mysql ?

We can reset the counter with following command :-

ALTER TABLE tablename AUTO_INCREMENT = 1

Read more...

How to install & configure SAR on CentOS/RHEL

How to install & configure SAR on CentOS/RHEL:-

SAR (System Activity Reporter) is an important tool that helps system administrator to get a review of the server box with status of different critical metrics at different points of time. Using sar command you can monitor performance of different Linux subsystems (CPU, Memory, I/O etc..) in real time. The sar command extracts and write to standard output records previously saved in a file.

How To Install SAR:-

You can install it using YUM command. Use following command to install SAR.

# yum install sysstat

How To Check SAR :-

Once installed, verify the sar version using following command:

# sar -V
 

sysstat version 9.0.4
(C) Sebastien Godard (sysstat  orange.fr)

Collect the statistics using cron job :-

Create sysstat file under /etc/cron.d directory that will collect the historical sar data.

# cat /etc/cron.d/sysstat

# Run system activity accounting tool every 10 minutes
*/10 * * * * root /usr/lib64/sa/sa1 1 1
# 0 * * * * root /usr/lib64/sa/sa1 600 6 &
# Generate a daily summary of process accounting at 23:53
53 23 * * * root /usr/lib64/sa/sa2 -A

1. So the first cron entry will run every 10 minutes which will call the sadc utility and collect system stats and store it in a binary file.
2. And the second cron entry will dump all the contents of that binary file into another text file.

By default 7 days statistics, So you can modify that HISTORY entry easily by editing the file.

# cat /etc/sysconfig/sysstat

# How long to keep log files (in days).
# If value is greater than 28, then log files are kept in
# multiple directories, one for each month.
HISTORY=28

By default all the statistics will save /var/log/sa location.

# cd /var/log/sa

 

Read more...

wget: unable to resolve host address `http'

wget: unable to resolve host address `http'

when i run wget command with any site than got below error

$ wget example.com 

--2016-07-18 15:34:12--  http://example.com/

Resolving http (http)... failed: Name or service not known.

wget: unable to resolve host address `http'


 Issue :- The issue is The DNS server seems out of order.
 Solution :- You can use another DNS server such as 8.8.8.8. and Put nameserver 8.8.8.8 to the first line of /etc/resolv.conf

Read more...

Backup Failed Mysqldump Got Errno 28 On Write

Backup Failed Mysqldump Got Errno 28 On Write

What is Error Message

Backup failed: mysqldump: Got errno 28 on write

Meaning of this error

Insufficient disk space to store output.

Solution of this error

Free up some disk space. You might want to track down the reason you're low on disk space too.

Read more...

How to repair MySQL databases and tables

How to repair MySQL databases and tables

To repair MySQL databases and tables there are below steps

1. Backing up the databases

Step 1: Stop mysql
         service mysqld stop

Step 2: Use Below command to copies all of the files from all of your databases to a directory name based on the current time (more precisely, the number of seconds elapsed since January 1, 1970). This ensures that each database backup is stored in a directory that has a unique name. For added protection, you can (and should) back up the database files to a remote location not on the server.

      cp -rfv /var/lib/mysql /var/lib/mysql$(date +%s)

Step 3: Start mysql
            service mysqld start

2. Checking and repairing a table with mysqlcheck 

   After you back up your databases, you are ready to start troubleshooting. The mysqlcheck program enables you to check and repair databases while MySQL is running. This feature is useful when you want to work on a database without stopping the entire MySQL service.

Additionally, mysqlcheck works on tables that use the MyISAM or InnoDB database engines.

Step 1. To use mysqlcheck  As the root user

           cd /var/lib/mysql

Step 2. Replacing DATABASE with the name of the database that you want to check:

           mysqlcheck DATABASE

The above command checks all of the tables in the specified database. Alternatively, to check a specific table in a database, type the following command. Replace DATABASE with the name of the database, and replace TABLE with the name of the table that you want to check:

           mysqlcheck DATABASE TABLE

Step 3: Mysqlcheck checks the specified database and tables. If a table passes the check, mysqlcheck displays OK for the table. However, if mysqlcheck reports an error for a table, type the following command to try to repair it. Replace DATABASE with the database name, and TABLE with the table name:

mysqlcheck -r DATABASE TABLE

Read more...

How To Run Nginx with different port other than 80

How To Run Nginx with different port other than 80

To start nginx via different port(other than 80) there are below steps.

Step 1: Open your config file
            vi /etc/nginx/conf.d/default.conf

Step 2: Change port number on which you are listening;
            listen       81;
           server_name  localhost;

Step 3: Add a rule to iptables
           vi /etc/sysconfig/iptables
           -A INPUT -m state --state NEW -m tcp -p tcp --dport 81 -j ACCEPT

Step 4: Restart IPtables
            service iptables restart;

Step 5: Restart the nginx server
            service nginx restart

Step 6: Now Access your nginx server files on port 81

Read more...

How To Install nginx on CentOS 6 Using yum

How To Install nginx on CentOS 6 with yum

there are below step to install nginx on centOS 6 using Yum Command.

Install From Repository

if you want to install nginx from repository than start from step 1 otherwise start from step 5.

Step 1. cd /etc/yum.repos.d

Step 2. Create a repo configuration file for Nginx

           vi nginx.repo

Step 3. Put the following lines to nginx.conf

           [nginx]

           name=nginx repo

           baseurl=http://nginx.org/packages/centos/$releasever/$basearch/

           gpgcheck=0

           enabled=1

Step 4. Save changes and exit the text editor.

Step 5. install EPEL

             EPEL is fo Extra Packages for Enterprise Linux. yum as a package manager does not include the latest version of nginx in its default repository, son need to install EPEL, will make sure that nginx on CentOS stays up to date.

          command prompt> sudo yum install epel-release

Step 6. Install nginx

           command prompt> sudo yum install nginx

Step 7. Start nginx

           command prompt> sudo /etc/init.d/nginx start

Configure Nginx

Step 1. Open the Nginx configuration file

           vi /etc/nginx/nginx.conf

Step 2. Modify the worker_process value to reflect the number of processor cores installed in the server.

           worker_processes 2

Step 3. To enable gzip compression, find the following line:

           #gzip on

          And uncomment it

          gzip on

Step 4. Save changes and exit the text editor.

    

Step 5. Restart the Nginx daemon to apply your changes.

            service nginx restart

Step 6. Configure Nginx to automatically start after reboot.

            chkconfig nginx on

Configure The Default Website

Step 1. Open the default website configuration file.

            vi /etc/nginx/conf.d/default.conf

Step 2. To set the listening port, find the following line and modify it’s value:

            listen 80;

Step 3. Set the DNS hostname of your website by finding the following line and replacing localhost with the name of your server.

           server_name localhost;

Step 4. The default website root directory is /usr/share/nginx/html. To change it, find the following lines and replace the highlighted value with the desired file path.

          location / { 

                  root /usr/share/nginx/html; 

                  index index.html index.htm; 

          }

5. To modify the default index file, add to or replace the values listed next to index.

6. Save changes and exit the text editor.

Configure Firewall To Allow HTTP Access

Step 1. Run the following command to allow HTTP access through IPTables.

            iptables -A INPUT -m state --state NEW -P tcp --dport 80 -j ACCEPT

Step 2. To permanently save the firewall rule, run the following command.

            /sbin/service iptables save

Read more...