An error occurred creating section handler for userSetting

Today when I try to reinstall the SQL Server 2012 cluster on virtual machine, it pop up an error message

Solution: Remove the Microsoft_Corporation folder at c:\users\{user_id}\AppData\Local

SQL Server alwayson step by step

We need at least 2 node, join the node1,node2 to domain, install stand-alone SQL Server 2012 on Node1 and Node2,remember dun choose the failover cluster installation

 Check Node1 SQL Server installed successfully.

 Node1 and Node2 still not clustered, the alwayson feature can't be enabled.

Check Node2 stand-alone SQL Server installed and alwayson feature can't be enabled.

 Create Node1 and Node2 cluster with failover cluster manager

 Add node1 and node2 to the cluster

 set Cluster IP address.

 Node1 and Node2 successful Clustered.

 

 Open SQL Server configuration manager, enable alwayson feature on node1 and restart SQL Server service.

 Open SQL Server configuration manager, enable alwayson feature on node2 and restart SQL Server service.

 Create a testing database AlwaysonTestDB on Node1

 Must be full recovery mode

 before configure the alwayson , do a full backup of AlwaysonTestDB

 Right click [Availability Groups] from SSMS and [New Availability Group Wizard]

 Set group name as group_alwayson

 Select the testing database AlwaysonTestDB

 Choose yes from Readable Secondary

 Configure SQL Server alwayson group IP and port, frontend application can use this IP to connect to database.

 Add node2 as a secondary replication SQL Server service.

 set secondary readable to yes.

 Specify a shared folder accessible by node1 and node2

 Alwayson group created successfully, check from SSMS panel, Node1 is the primary, Node2 is the secondary.

 Check from Node2 SSMS, the AlwaysonTestDB Synchronized.

 do a testing on node1, create a table t and insert sample data.

 Connect to the alwayson group IP from another SQL Server SSMS(it's not node1 and node2)

 Connect successful and also can search the testing data.

 shutdown node1 to test failover.

 The alwayson  IP still can connect, data can search also. now we insert a new record to table t.
insert into t select 4,'d', because node1 has been shutdown, so it should be inserted to node2

 once node1 start up again, the new record has sync from node2 to  Node1 already, good.

 But now the node1 become secondary, Node2 is primary node.

 

 

 

 Looks like it's a really good feature for SQL Server read-write split.

 

Centos connect to windows sharing folder

[dbaroot@linux ~]$ sudo -s
[sudo] password for dbaroot:
[root@linux dbaroot]# yum install samba samba-client cifs-utils
Loaded plugins: fastestmirror, security
Determining fastest mirrors
 * base: ftp.cs.pu.edu.tw
 * extras: ftp.cs.pu.edu.tw
 * updates: ftp.cs.pu.edu.tw
base                                                                                                                                                      | 3.7 kB     00:00
extras                                                                                                                                                    | 3.4 kB     00:00
updates                                                                                                                                                   | 3.4 kB     00:00
updates/primary_db                                                                                                                                        | 3.8 MB     00:03
Setting up Install Process
Package samba-client-3.6.9-151.el6.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package samba.x86_64 0:3.6.9-151.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=================================================================================================================================================================================
 Package                                 Arch                                     Version                                           Repository                              Size
=================================================================================================================================================================================
Installing:
 samba                                   x86_64                                   3.6.9-151.el6                                     base                                   5.0 M
Transaction Summary
=================================================================================================================================================================================
Install       1 Package(s)
Total download size: 5.0 M
Installed size: 18 M
Is this ok [y/N]: y
Downloading Packages:
samba-3.6.9-151.el6.x86_64.rpm                                                                                                                            | 5.0 MB     00:04
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : samba-3.6.9-151.el6.x86_64                                                                                                                                    1/1
  Verifying  : samba-3.6.9-151.el6.x86_64                                                                                                                                    1/1
Installed:
  samba.x86_64 0:3.6.9-151.el6
Complete!
[root@linux dbaroot]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
[root@linux dbaroot]# vi /etc/hosts
[root@linux dbaroot]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6\\100.170.1.123\db_backups storage.test.com storage # add this new line to /etc/hosts
[root@linux dbaroot]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sde3             152G  3.3G  141G   3% /
/dev/mapper/mpathc    197G  469M  187G   1% /mnt/mysql
[root@linux dbaroot]# mkdir /mnt/storage
[root@linux dbaroot]# vi /etc/fstab;
[root@linux dbaroot]# cat /etc/fstab;
#
# /etc/fstab
# Created by anaconda on Thu Jun 13 02:35:31 2013
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
UUID=880a3cb6-0655-4e17-873b-c60a2e5c6cad /                       ext4    defaults        1 1
UUID=4e412c25-bad4-4cf0-b1fc-e3bc393cfacc /boot                   ext4    defaults        1 2
UUID=405ad5ac-ea7f-4451-834b-43db33ff4d38 /data                   ext4    defaults        1 2
UUID=989614ce-fa27-4c2b-a271-997e1bace4fd swap                    swap    defaults        0 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
/dev/mapper/mpathc      /mnt/mysql              ext4    defaults        0 0
//storage\db_backups /mnt/storage cifs exec,credentials=/etc/cifspw 0 0 #add this line to /etc/fstab
[root@linux dbaroot]# chmod 600 /etc/cifspw
[root@linux mnt]# vi /etc/cifspw   #create a password file including share folder login user and password
username=dbbakup_user
password=backup_password
[root@linux mnt]# mount /mnt/storage
[root@linux mnt]# cd storage
[root@linux storage]# mkdir 100.10.88.68
[root@linux storage]# ls -la
total 1
drwxrwxrwx. 2 1036 users    0 Jul 23 12:24 100.10.88.68

MySQL index on between query

Yesterday engineer pass me a very simple query, 2 tables ip and country
mysql> desc ip;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| address_start | varchar(16) | NO   |     | NULL    |                |
| address_end   | varchar(16) | NO   |     | NULL    |                |
| number_start  | bigint(20)  | NO   |     | NULL    |                |
| number_end    | bigint(20)  | NO   | MUL | NULL    |                |
| country_code  | varchar(3)  | NO   |     | NULL    |                |
| city_id       | bigint(20)  | NO   |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> desc country;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(64) | NO   | UNI | NULL    |       |
| code    | varchar(3)  | NO   | PRI | NULL    |       |
| blocked | char(1)     | NO   |     | Y       |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MySQL>
 

MySQL root user can't grant privileges

Today setup MySQL for developers, when I try to create a new user for application, encounter  1045 error , it's because the root user dun have GRANT_PRIV rights. I paste the solution here.

mysql> grant all privileges on *.* to 'ipservice_usr'@'192.*.*.*' identified by '********';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '**************' |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                         |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE User='root' and Host='localhost'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: ****************
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: N
1 row in set (0.00 sec)

mysql> update mysql.user set grant_priv='Y' where user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,grant_priv from mysql.userl
    -> ;
ERROR 1146 (42S02): Table 'mysql.userl' doesn't exist
mysql> select user,grant_priv from mysql.user;
+------+------------+
| user | grant_priv |
+------+------------+
| root | Y          |
| root | Y          |
| root | Y          |
+------+------------+
3 rows in set (0.00 sec)
mysql> exit
Bye
[root@IBM-09 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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> grant all privileges on *.* to 'ipservice_usr'@'********' identified by '********';
Query OK, 0 rows affected (0.00 sec)
mysql>

MySQL innodb_flush_log_at_trx_commit parameter

Today engineer try to import 3 millions data from dump file, it's very very slow, check the configuration, innodb_buffer_pool_size only set to 128M, reset to 4G and test again , still very slow, suddenly remember the parameter innodb_flush_log_at_trx_commit, the MySQL server using the default value = 1, set this parameter to 2, import become very fast.

Here paste the description from MySQL website

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1, the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.
The default value of this variable is 1 (prior to MySQL 4.0.13, the default is 0).
A value of 1 is required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value.

Create Shared Storage with iSCSI target

Step 1: Open Windows 2012 Server Manager, go to the [File and Storage Services] ->iSCSI.

 

Step 2: New iSCSI Virtual Disk.

Step 3: Connect the share Storage from iSCSI initiator

Run iSCSI initiator from machine DBAClusterNode1, select the IQN created just now and connect.

 

Step 4: Open Computer Management and click [Disk Management], the shared harddisk 5GB already there.

 

Step 5: Follow previously step add 2 more shared hard disk(20G and 15G)

 

 

Step 6: Online shared hard disk

 

 

 

Step 7: Initialize disk

 

 

Step 8: Create volume for hard disk

 

Step 9: Share hard disk successfully created.