這次來整理關於C# Entity framework 搭配Mysql 資料庫的一些使用方式：

1.  先安裝以下套件

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Pomelo.EntityFrameworkCore.MySql

2. 建立Dbcontext的模型

 public class RonWebDbContext: DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
        	// 配置連線
            string connectionString = "連線字串";
            optionsBuilder.UseMySql(connectionString, new MySqlServerVersion(new Version(8, 0, 33)),
            options => options.EnableRetryOnFailure());
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        	// 配置雙主鍵
            modelBuilder.Entity<RefreshTokenLog>()
                .HasKey(a => new { a.RefreshToken, a.UserId });
        }
		
		// 資料庫每一張表
        public DbSet<UserMain> UserMain { get; set; }
        public DbSet<RefreshTokenLog> RefreshTokenLog { get; set; }
        
    }

3. 雙主鍵表及關連表範例

    public class RefreshTokenLog
    {
        /// <summary>
        /// RefreshToken
        /// </summary>
        [Key]
        public string RefreshToken { get; set; } = string.Empty;
        
        [Key]
        public long UserId { get; set; }
        
        /// 關連表
        [ForeignKey("UserId")]
        public virtual UserMain? UserMain { get; set; }
        
        [Required]
        public DateTime ExpirationDate { get; set; }
        
        [Required]
        public DateTime CreateDate { get; set; }
    }

4. 自動增值表範例

    public class UserMain
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long UserId { get; set; }

        [Required]
        [StringLength(50)]
        public string Account { get; set; } = string.Empty;

        [Required]
        public string Password { get; set; } = string.Empty;

        [Required]
        [StringLength(20)]
        public string UserName { get; set; } = string.Empty;

        [StringLength(250)]
        [Required]
        public string? Email { get; set; }

        [Required]
        public DateTime CreateDate { get; set;}

        public DateTime? UpdateDate { get; set; }
    }

 5. 透過指令來先製作遷移檔

 dotnet ef migrations add "更新說明"

6. 更新資料庫

dotnet ef database update

如果有環境變數需求

環境變數連線字串名稱="連線字串" dotnet ef database update

連線字串的範例如下

"server=＄host;port=$port;user id=$user;password=$pwd;database=$db;charset=utf8;"

 以上就為建立C#及Mysql資料庫的整個流程

後續使用就跟EntityFramework操作方式相同

using (var db = new RonWebDbContext())
{
	var data = new RefreshTokenLog()
    {
        RefreshToken = "RefreshToken",
    	UserId = 1,
        ExpirationDate = DateTime.Now,
        CreateDate = DateTime.Now
    };
    await db.RefreshTokenLog.AddAsync(data);
    await db.SaveChangesAsync();
}

遠端備份sql

$ 為變數，注意$pwd得地方不能有間隔

echo mysqldump -h $host --port $port -u $user -p$pwd --databases $db > backup_$(date +"%Y%m%d_%H%M%S")_$backupName.sql